package com.csmy.my.center.util.db;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import com.csmy.my.center.module.CustomerInfo;
import com.csmy.my.center.module.MemeberInfo;
import com.csmy.my.center.util.CTConstants;
import com.csmy.my.center.util.CTUtils;
import com.csmy.my.center.util.FileUtils;
import com.csmy.my.center.util.PageModel;
import com.csmy.my.center.util.StringUtil;
import com.csmy.my.center.util.UniqueID;
import com.csmy.my.center.util.base.Channel;
import com.csmy.my.center.util.dataconvert.Dto;
import com.csmy.my.center.util.dataconvert.impl.BaseDto;

//数据库操作工具类(前台使用)
@SuppressWarnings("unchecked")
public class JdbcTemplateTool {
	
	public static void main(String[] args) {
		System.out.println(" b'b".replaceAll("'", "''"));
		
	}
	//分页基本类
	public static PageModel pager = new PageModel();
	
	
	/**
	 * 增加用戶登录日志
	 * @param pDto
	 * @return
	 */
	public static boolean addLoginLog(Dto paramDto) {
		boolean temp = false;
		DBManager dbManager = new DBManager();
		try {
			String sqlString = " insert into wxb_login_log(user_id,login_ip,login_time,log_type,ismobile)values(";
			if(StringUtil.isNotEmpty(paramDto.getAsString("user_id"))){
			sqlString +=" '"+paramDto.getAsString("user_id")+"',";
			}
			
			if(StringUtil.isNotEmpty(paramDto.getAsString("login_ip"))){
				sqlString +=" '"+paramDto.getAsString("login_ip")+"',";
			}
			
			if(StringUtil.isNotEmpty(paramDto.getAsString("login_time"))){
				sqlString +=" '"+paramDto.getAsString("login_time")+"',";
			}
			
			if(StringUtil.isNotEmpty(paramDto.getAsString("log_type"))){
				sqlString +=" '"+paramDto.getAsString("log_type")+"',";
			}else {
				sqlString +=" 0,";
			}
			
			if(StringUtil.isNotEmpty(paramDto.getAsString("ismobile"))){
				sqlString +=" '"+paramDto.getAsString("ismobile")+"')";
			}else {
				sqlString +=" 0)";
			}
			
			StringUtil.xprint("updateSQL="+sqlString);
			temp = dbManager.execute(sqlString);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return temp;
	}
	
	/**
	 * 
	 * @param visit_code
	 * @return
	 */
	public static List<Dto> getMemberLoginLog(String visit_code) {
		boolean temp = false;
		DBManager dbManager = new DBManager();
		try {
			
			
			
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	
	/**
	 * 通过ID获取会员信息
	 * @param memeber_id
	 * @return
	 */
	public static MemeberInfo getMemeberInfo(String memeber_id) {
		MemeberInfo memberInfo = null;
		DBManager dbManager = new DBManager();
		try {
			String sqlString = "select * from wxb_memeber t where t.memeber_id='"+memeber_id+"' ";
			List<Dto> mList = dbManager.queryForList(sqlString);
			if(CTUtils.isNotEmpty(mList)){
				Dto userDto = mList.get(0);
				memberInfo = new MemeberInfo();
				memberInfo.setMemeber_id(userDto.getAsString("memeber_id"));
                memberInfo.setAccount(userDto.getAsString("account"));
                memberInfo.setName(userDto.getAsString("name"));
                memberInfo.setPassword(userDto.getAsString("password"));
                memberInfo.setEmail(userDto.getAsString("email"));
                memberInfo.setPay_account(userDto.getAsString("pay_account"));
                memberInfo.setQq_num(userDto.getAsString("qq_num"));
                memberInfo.setPhone(userDto.getAsString("phone"));
                memberInfo.setRecom_user(userDto.getAsString("recom_user"));
                memberInfo.setRegister_time(userDto.getAsString("register_time"));
                memberInfo.setVisit_code(userDto.getAsString("visit_code"));
                memberInfo.setLevel_code(userDto.getAsString("level_code"));
                memberInfo.setEpassword(CTUtils.decryptBasedDes(userDto.getAsString("password")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return memberInfo;
	}
	
	
	/**
	 * 获取同一个手机号码购买次数
	 * @param phone
	 * @param good_id
	 * @return
	 */
	public static int getUserBuyCountByPhoneNum(String phone,String good_id) {
		int count = 0;
		DBManager dbManager = new DBManager();
		try {
			String sql = "select * from wxb_order t where t.buyer_phone='"+phone+"' and t.good_id='"+good_id+"' ";
			List<Dto> cList = dbManager.queryForList(sql);
			if(CTUtils.isNotEmpty(cList)){
			   count = cList.size();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	
	
	/**
	 * 获取单页推广文案内容
	 * @param spcid
	 * @return
	 */
	public static Dto getSPCCopyInfo(String spcid) {
		Dto copyDto = null;
		try {
			List<Dto> copyList = getTableList("wxb_good_copy", " t.copy_id in("+spcid+") and t.type_id=1");
			if(!StringUtil.checkListBlank(copyList)){
				copyDto = copyList.get(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return copyDto;
	}
	
	
	/**
	 * 通过ID获取商户信息
	 * @param memeber_id
	 * @return
	 */
	public static CustomerInfo getCustomerInfo(String customer_id) {
		CustomerInfo customerInfo = null;
		DBManager dbManager = new DBManager();
		try {
			String sqlString = "select * from wxb_customer t where t.customer_id='"+customer_id+"' ";
			List<Dto> mList = dbManager.queryForList(sqlString);
			
			if(CTUtils.isNotEmpty(mList)){
				Dto userDto = mList.get(0);
				 customerInfo = new CustomerInfo();
                 customerInfo.setCustomer_id(customer_id);
                 customerInfo.setLogin_name(userDto.getAsString("login_name"));
                 customerInfo.setCustomer_name(userDto.getAsString("customer_name"));
                 customerInfo.setLogin_pwd(userDto.getAsString("login_pwd"));
                 customerInfo.setQQ(userDto.getAsString("QQ"));
                 customerInfo.setState(userDto.getAsString("state"));
                 customerInfo.setWxh(userDto.getAsString("wxh"));
                 customerInfo.setPhone(userDto.getAsString("phone"));
                 customerInfo.setLevel(userDto.getAsString("level"));
                 customerInfo.setCreatetime(userDto.getAsString("createtime"));
                 customerInfo.setUpdate_time(userDto.getAsString("update_time"));
                 customerInfo.setEpassword(CTUtils.decryptBasedDes(userDto.getAsString("login_pwd")));
                 customerInfo.setState_name(CTUtils.getCodeName("CUSTATE", userDto.getAsString("state")));
                 customerInfo.setLevel_name(CTUtils.getCodeName("CULEVEL", userDto.getAsString("level")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return customerInfo;
	}
	
	/**
	 * 通过邀请码获取会员信息
	 * @param memeber_id
	 * @return
	 */
	public static MemeberInfo getMemeberInfoByCode(String visit_code) {
		MemeberInfo memberInfo = null;
		DBManager dbManager = new DBManager();
		try {
			String sqlString = "select * from wxb_memeber t where t.visit_code='"+visit_code+"' ";
			List<Dto> mList = dbManager.queryForList(sqlString);
			if(CTUtils.isNotEmpty(mList)){
				Dto userDto = mList.get(0);
				memberInfo = new MemeberInfo();
				memberInfo.setMemeber_id(userDto.getAsString("memeber_id"));
                memberInfo.setAccount(userDto.getAsString("account"));
                memberInfo.setName(userDto.getAsString("name"));
                memberInfo.setPassword(userDto.getAsString("password"));
                memberInfo.setEmail(userDto.getAsString("email"));
                memberInfo.setPay_account(userDto.getAsString("pay_account"));
                memberInfo.setQq_num(userDto.getAsString("qq_num"));
                memberInfo.setPhone(userDto.getAsString("phone"));
                memberInfo.setRecom_user(userDto.getAsString("recom_user"));
                memberInfo.setRegister_time(userDto.getAsString("register_time"));
                memberInfo.setVisit_code(userDto.getAsString("visit_code"));
                memberInfo.setLevel_code(userDto.getAsString("level_code"));
                memberInfo.setUpdate_time(userDto.getAsString("update_time"));
                memberInfo.setEpassword(CTUtils.decryptBasedDes(userDto.getAsString("password")));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return memberInfo;
	}
	
	//修改会员信息
	public static boolean updateMemeberInfo(Dto paramDto) {
		boolean bol = false;
		DBManager dbManager = new DBManager();
		try {
			String sqlString = "update wxb_memeber t set ";

			if(StringUtil.isNotEmpty(paramDto.getAsString("phone"))){
				sqlString +=" t.phone = '"+paramDto.getAsString("phone")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("qq_num"))){
				sqlString +=" t.qq_num = '"+paramDto.getAsString("qq_num")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("email"))){
				sqlString +=" t.email = '"+paramDto.getAsString("email")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("account"))){
				sqlString +=" t.account = '"+paramDto.getAsString("account")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("recom_user"))){
				sqlString +=" t.recom_user = '"+paramDto.getAsString("recom_user")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("visit_code"))){
				sqlString +=" t.visit_code = '"+paramDto.getAsString("visit_code")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("level_code"))){
				sqlString +=" t.level_code = '"+paramDto.getAsString("level_code")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("password"))){
				sqlString +=" t.password = '"+paramDto.getAsString("password")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("name"))){
				sqlString +=" t.name = '"+paramDto.getAsString("name")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("update_time"))){
				sqlString +=" t.update_time = '"+paramDto.getAsString("update_time")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("pay_account"))){
				sqlString +=" t.pay_account = '"+paramDto.getAsString("pay_account")+"',";
			}
			
			sqlString +=" t.memeber_id = '"+paramDto.getAsString("memeber_id")+"'";
			
			sqlString+=" where t.memeber_id = '"+paramDto.getAsString("memeber_id")+"'";
			StringUtil.xprint("updateSQL="+sqlString);

			bol = dbManager.execute(sqlString);
			

		}catch (Exception e) {
			e.printStackTrace();
		}
		
		return bol;
	}
	
	//修改商户信息
	public static boolean updateCustomerInfo(Dto paramDto) {
		boolean bol = false;
		DBManager dbManager = new DBManager();
		try {
			String sqlString = "update wxb_customer t set ";

			if(StringUtil.isNotEmpty(paramDto.getAsString("phone"))){
				sqlString +=" t.customer_name = '"+paramDto.getAsString("customer_name")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("qq_num"))){
				sqlString +=" t.QQ = '"+paramDto.getAsString("qq_num")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("phone"))){
				sqlString +=" t.phone = '"+paramDto.getAsString("phone")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("login_name"))){
				sqlString +=" t.login_name = '"+paramDto.getAsString("login_name")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("update_time"))){
				sqlString +=" t.update_time = '"+paramDto.getAsString("update_time")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("state"))){
				sqlString +=" t.state = '"+paramDto.getAsString("state")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("level"))){
				sqlString +=" t.level = '"+paramDto.getAsString("level")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("login_pwd"))){
				sqlString +=" t.login_pwd = '"+paramDto.getAsString("login_pwd")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("wxh"))){
				sqlString +=" t.wxh = '"+paramDto.getAsString("wxh")+"',";
			}
			
			sqlString +=" t.customer_id = '"+paramDto.getAsString("customer_id")+"'";
			
			sqlString+=" where t.customer_id = '"+paramDto.getAsString("customer_id")+"'";
			StringUtil.xprint("updateSQL="+sqlString);

			bol = dbManager.execute(sqlString);
			

		}catch (Exception e) {
			e.printStackTrace();
		}
		
		return bol;
	}
	
	/**
	 * 验证会员登录
	 * @param paramDto
	 * @return
	 */
	public static MemeberInfo checkMUserLogin(Dto paramDto){
		MemeberInfo memberInfo = null;
		DBManager dbManager = new DBManager();
		try {
			String name = paramDto.getAsString("login_name");
			name=name.replaceAll("'", "''");
			String sqlString = "select * from wxb_memeber t where t.account='"+name
			+"' and t.password='"+paramDto.getAsString("login_pword")+"'";
			List<Dto> mList = dbManager.queryForList(sqlString);
			if(CTUtils.isNotEmpty(mList)){
				Dto userDto = mList.get(0);
				memberInfo = new MemeberInfo();
				memberInfo.setMemeber_id(userDto.getAsString("memeber_id"));
                memberInfo.setAccount(userDto.getAsString("account"));
                memberInfo.setName(userDto.getAsString("name"));
                memberInfo.setPassword(userDto.getAsString("password"));
                memberInfo.setEmail(userDto.getAsString("email"));
                memberInfo.setPay_account(userDto.getAsString("pay_account"));
                memberInfo.setQq_num(userDto.getAsString("qq_num"));
                memberInfo.setPhone(userDto.getAsString("phone"));
                memberInfo.setRecom_user(userDto.getAsString("recom_user"));
                memberInfo.setRegister_time(userDto.getAsString("register_time"));
                memberInfo.setVisit_code(userDto.getAsString("visit_code"));
                memberInfo.setLevel_code(userDto.getAsString("level_code"));
                memberInfo.setUse_recom(userDto.getAsString("use_recom"));
                memberInfo.setUpdate_time(userDto.getAsString("update_time"));
                memberInfo.setEpassword(CTUtils.decryptBasedDes(userDto.getAsString("password")));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return memberInfo;
		
	}
	
	
	/**
	 * 验证商户登录
	 * @param paramDto
	 * @return
	 */
	public static CustomerInfo checkCUserLogin(Dto paramDto){
		CustomerInfo customerInfo = null;
		DBManager dbManager = new DBManager();
		try {
			String sqlString = "select * from wxb_customer t where t.login_name='"+paramDto.getAsString("login_name")
			+"' and t.login_pwd='"+paramDto.getAsString("login_pword")+"'";
			List<Dto> mList = dbManager.queryForList(sqlString);
			if(CTUtils.isNotEmpty(mList)){
				Dto userDto = mList.get(0);
				customerInfo = new CustomerInfo();
                 customerInfo.setCustomer_id(userDto.getAsString("customer_id"));
                 customerInfo.setCustomer_name(userDto.getAsString("customer_name"));
                 customerInfo.setLogin_name(userDto.getAsString("login_name"));
                 customerInfo.setLogin_pwd(userDto.getAsString("login_pwd"));
                 customerInfo.setQQ(userDto.getAsString("QQ"));
                 customerInfo.setPhone(userDto.getAsString("phone"));
                 customerInfo.setWxh(userDto.getAsString("wxh"));
                 customerInfo.setState(userDto.getAsString("state"));
                 customerInfo.setLevel(userDto.getAsString("level"));
                 customerInfo.setCreatetime(userDto.getAsString("createtime"));
                 customerInfo.setUpdate_time(userDto.getAsString("update_time"));
                 customerInfo.setEpassword(CTUtils.decryptBasedDes(userDto.getAsString("login_pwd")));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return customerInfo;
		
	}
	
	//用户注册
	public static boolean userRegister(Dto paramDto) {
		System.out.println("");
		boolean bol = false;
		String sqlString = null;
		DBManager dbManager = new DBManager();
		try {
			//String visit_code = UniqueID.getUniqueID(4, 2);//会员邀请码
			sqlString = "insert into wxb_memeber(memeber_id,name,account,password,pay_account,qq_num,email,phone,recom_user,register_time,update_time,use_recom,level_code,visit_code)values(";
			sqlString +="'"+paramDto.getAsString("memeber_id")+"',";
			String name = paramDto.getAsString("name");
			name=name.replaceAll("'", "''");
			sqlString +="'"+name+"',";
			String acc = paramDto.getAsString("account");
			acc=acc.replaceAll("'", "''");
			sqlString +="'"+acc+"',";
		
			sqlString +="'"+paramDto.getAsString("password")+"',";
			sqlString +="'"+paramDto.getAsString("pay_account")+"',";
			sqlString +="'"+paramDto.getAsString("qq_num")+"',";
			sqlString +="'"+paramDto.getAsString("email")+"',";
			sqlString +="'"+paramDto.getAsString("phone")+"',";
			sqlString +="'"+paramDto.getAsString("recom_user")+"',";
			sqlString +="'"+CTUtils.getCurrentTime()+"',";
			sqlString +="'"+CTUtils.getCurrentTime()+"',";
			sqlString +="'"+paramDto.getAsString("use_vlink")+"',";//邀请功能
			sqlString +="'"+paramDto.getAsString("level_code")+"',";
			sqlString +="'"+paramDto.getAsString("visit_code")+"')";
			
			bol = dbManager.insertBySQL(sqlString);
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			//处理记录日志
			Dto logDto =new BaseDto();
			logDto.put("otype", "INSERT");
			logDto.put("msql_text", sqlString);
			logDto.put("createtime", CTUtils.getCurrentTime());
			logDto.put("state", bol==true?"0":"1");
			addMsSqlLog(logDto); 
		}
		
		return bol;
	}
	
	//商户注册
	public static boolean customRegister(Dto paramDto) {
		boolean bol = false;
		String sqlString = null;
		DBManager dbManager = new DBManager();
		try {
			sqlString = "insert into wxb_customer(customer_id,customer_name,QQ,wxh,phone,login_name,login_pwd,state,level,update_time,createtime)values(";
			sqlString +="'"+paramDto.getAsString("customer_id")+"',";
			sqlString +="'"+paramDto.getAsString("customer_name")+"',";
			sqlString +="'"+paramDto.getAsString("qq_num")+"',";
			sqlString +="'"+paramDto.getAsString("wxh")+"',";
			sqlString +="'"+paramDto.getAsString("phone")+"',";
			sqlString +="'"+paramDto.getAsString("login_name")+"',";
			sqlString +="'"+paramDto.getAsString("login_pwd")+"',";
			sqlString +="'"+paramDto.getAsString("state")+"',";
			sqlString +="'"+paramDto.getAsString("level")+"',";
			sqlString +="'"+CTUtils.getCurrentTime()+"',";
			sqlString +="'"+CTUtils.getCurrentTime()+"')";
			
			bol = dbManager.insertBySQL(sqlString);
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			//处理记录日志
			Dto logDto =new BaseDto();
			logDto.put("otype", "INSERT");
			logDto.put("msql_text", sqlString);
			logDto.put("createtime", CTUtils.getCurrentTime());
			logDto.put("state", bol==true?"0":"1");
			addMsSqlLog(logDto); 
		}
		
		return bol;
	}
	
	//验证会员账户是否已经存在
	public static boolean checkAccountExsit(String username,String phone) {
		boolean bool = false;
		DBManager dbManager = new DBManager();
		try {
			String sql = "select * from wxb_memeber t where 1=1 ";
			if(StringUtil.isNotEmpty(username)){
				sql+=" and (t.account = '"+username+"'";
			}
			if(StringUtil.isNotEmpty(phone)){
				sql+=" or t.phone = '"+phone+"'";
			}
			
			sql += ")";
			
			StringUtil.xprint("regsql="+sql);
			List<Dto> cList = dbManager.queryForList(sql);
			if(!StringUtil.checkListBlank(cList)){
				bool = true;
			}else{
				bool = false;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return bool;
	}
	
	//验证商户账户是否已经存在
	public static boolean checkCAccountExsit(String username,String cust_name,String phone) {
		boolean bool = false;
		DBManager dbManager = new DBManager();
		try {
			String sql = "select * from wxb_customer t where 1=1 ";
			if(StringUtil.isNotEmpty(username)){
				sql+=" and (t.login_name = '"+username+"'";
			}
			if(StringUtil.isNotEmpty(cust_name)){
				sql+=" or t.customer_name = '"+cust_name+"'";
			}
			
			if(StringUtil.isNotEmpty(phone)){
				sql+=" or t.phone = '"+phone+"'";
			}
			
			sql += ")";
			
			List<Dto> cList = dbManager.queryForList(sql);
			if(!StringUtil.checkListBlank(cList)){
				bool = true;
			}else{
				bool = false;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
		
		return bool;
	}
	
	/**
	 * 获取字典信息
	 * @param field_code 对照字段
	 * @return
	 */
	public  static List<Dto> getCodeList(String field_code) {
		List<Dto> codeList = null;
		DBManager dbManager = new DBManager();
		try {
			
			if(StringUtil.isNotBlank(field_code)){
			   String sqlString = "select * from sys_code t where t.field_code='"+field_code+"'";
			   StringUtil.xprint("sql="+sqlString);
			   codeList = dbManager.queryForList(sqlString);
			}
		
		} catch (Exception e) {
			e.printStackTrace();
			StringUtil.xprint("查询字典信息出错...");
		}
		return codeList;
		
	}
	
	/**
	 * 通用List数据查询接口
	 * @param tableName
	 * @param whereStr
	 * @return
	 */
	public  static List<Dto> getTableList(String tableName,String whereStr) {
		List<Dto> tableList = null;
		DBManager dbManager = new DBManager();
		try {
			String sqlString ="";
			if(StringUtil.isNotBlank(tableName)){
				sqlString = "select * from "+tableName+" t where 1=1 ";
			}
			if(StringUtil.isNotBlank(whereStr)){
				sqlString += " and "+ whereStr;
			}
			
			StringUtil.xprint("sql="+sqlString);
			tableList = dbManager.queryForList(sqlString);
		} catch (Exception e) {
			e.printStackTrace();
			StringUtil.xprint("查询字典信息出错...");
		}
		return tableList;
		
	}
	
	/**
	 * 获取商品分类
	 * @return
	 */
	public  static List<Dto> getGoodTypeList() {
		List<Dto> typeList = null;
		 try {
			 
			 typeList = getTableList("wxb_good_type", null);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return typeList;

	}
	
	/**
	 * 获取商户订单集合
	 * @return
	 */
	public static PageModel getCTOrderList(Dto paramDto) {
		List<Dto> tableList = null;
		DBManager dbManager = new DBManager();
		 try {
			 
			 int pageSize = 0;
			if(CTUtils.isEmpty(paramDto.getAsInteger("pageSize"))){
				pageSize = CTConstants.PAGER_MSIZE;
			}else{
				pageSize = paramDto.getAsInteger("pageSize");
			}
			 int start = 0;
			 if(CTUtils.isNotEmpty(paramDto.getAsInteger("pager.offset"))){
				start = paramDto.getAsInteger("pager.offset");
			 }
			 String sqlString =" select t.*, "+
				 " t2.good_name, "+
				 " t3.sku_name, "+
				 " t3.sku_price, "+
				 " t3.sku_pmoney, "+
				 " t4.customer_name, "+
				 " t4.wxh, "+
				 " t4.QQ, "+
				 " t5.name as user_name, "+
				 " t5.email, "+
				 " t5.account, "+
				 " t5.qq_num, "+
				 " t5.phone, "+
				 " (select t1.channel_name "+
				 " from wxb_channel t1 "+
				 " where t.channel_id = t1.channel_id) as channel_name "+
				 " from wxb_order t, "+
				 " wxb_good     t2, "+
				 " wxb_good_sku t3, "+
				 " wxb_customer t4, "+
				 " wxb_memeber  t5 "+
				 " where t.good_id = t2.good_id "+
				 " and t.sku_id = t3.sku_id "+
				 " and t2.customer_id = t4.customer_id "+
				 " and t.user_id = t5.memeber_id ";
				 if(CTUtils.isNotEmpty(paramDto.getAsString("customer_id"))){
				    sqlString += " and t4.customer_id='"+paramDto.getAsString("customer_id")+"' ";
				 }
				 if(CTUtils.isNotEmpty(paramDto.getAsString("order_id"))){
					sqlString += " and ( t.order_id in ('"+paramDto.getAsString("order_id")+"') or t.oid in ('"+paramDto.getAsString("order_id")+"')) ";
				 }
				 if(CTUtils.isNotEmpty(paramDto.getAsString("state_id"))){
					sqlString += " and t.state='"+paramDto.getAsString("state_id")+"' ";
				 }
				 if(CTUtils.isNotEmpty(paramDto.getAsString("buyer_phone"))){
					sqlString += " and t.buyer_phone like '%"+paramDto.getAsString("buyer_phone")+"%' ";
				 }
				 if(CTUtils.isNotEmpty(paramDto.getAsString("buyer_name"))){
					sqlString += " and t.buyer_name like '%"+paramDto.getAsString("buyer_name")+"%' ";
				 }
				 if(CTUtils.isNotEmpty(paramDto.getAsString("good_name"))){
					sqlString += " and t2.good_name like '%"+paramDto.getAsString("good_name")+"%' ";
				 }
				 if(CTUtils.isNotEmpty(paramDto.getAsString("account"))){
					sqlString += " and ( t5.account like '%"+paramDto.getAsString("account")+"%' or t5.name like '%"+paramDto.getAsString("account")+"%') ";
				 }
				 if(CTUtils.isNotEmpty(paramDto.getAsString("start_date"))){
	            	sqlString += " and t.order_time >= '"+paramDto.getAsString("start_date")+" 00:00:00'";
	             }

                 if(CTUtils.isNotEmpty(paramDto.getAsString("end_date"))){
            	    sqlString += " and t.order_time <= '"+paramDto.getAsString("end_date")+" 23:59:59'";
                 }
				 
				 if(CTUtils.isNotEmpty(paramDto.getAsString("alisaname"))){
				    sqlString += 
					    	" and (t.buyer_phone like '%"+paramDto.getAsString("alisaname")+"%' "+
					    	" or t.buyer_name like '%"+paramDto.getAsString("alisaname")+"%' "+
					    	" or t2.good_name like '%"+paramDto.getAsString("alisaname")+"%' "+
					    	" or t.courier_id like '%"+paramDto.getAsString("alisaname")+"%' )";
				 }
			   
			 sqlString += " order by t.order_time desc";
			 //处理分页
			 int totalCount = dbManager.getTotalCount(sqlString);
			 sqlString+=" limit "+start+","+pageSize;
			 StringUtil.xprint("sql="+sqlString);
			 tableList = dbManager.queryForList(sqlString);
			 if(!StringUtil.checkListBlank(tableList)){
				 for (Dto dto : tableList) {
					dto.put("state_name", CTUtils.getCodeName("ORDER_STATE", dto.getAsString("state")));
					dto.put("type_name", CTUtils.getCodeName("ORDER_TYPE", dto.getAsString("order_type")));
				}
			 }
			
			pager = dbManager.getPageModel(tableList, start,pageSize,totalCount);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return pager;
	}
	
	
	/**
	 * 获取商户订单合计
	 * @return
	 */
	public static Dto getCTOrderCount(Dto paramDto) {
		 Dto countDto = null;
		 DBManager dbManager = new DBManager();
		 try {

			 String sqlString =" select t.*, "+
				 " t2.good_name, "+
				 " t3.sku_name, "+
				 " t3.sku_cost, "+
				 " t3.sku_price, "+
				 " t3.sku_pmoney, "+
				 " t4.customer_name, "+
				 " t4.wxh, "+
				 " t4.QQ, "+
				 " t5.name as user_name, "+
				 " t5.email, "+
				 " t5.account, "+
				 " t5.qq_num, "+
				 " t5.phone, "+
				 " (select t1.channel_name "+
				 " from wxb_channel t1 "+
				 " where t.channel_id = t1.channel_id) as channel_name "+
				 " from wxb_order t, "+
				 " wxb_good     t2, "+
				 " wxb_good_sku t3, "+
				 " wxb_customer t4, "+
				 " wxb_memeber  t5 "+
				 " where t.good_id = t2.good_id "+
				 " and t.sku_id = t3.sku_id "+
				 " and t2.customer_id = t4.customer_id "+
				 " and t.user_id = t5.memeber_id ";
			     if(CTUtils.isNotEmpty(paramDto.getAsString("customer_id"))){
				    sqlString += " and t4.customer_id='"+paramDto.getAsString("customer_id")+"' ";
				 }
				 if(CTUtils.isNotEmpty(paramDto.getAsString("order_id"))){
					sqlString += " and ( t.order_id='"+paramDto.getAsString("order_id")+"' or t.oid='"+paramDto.getAsString("order_id")+"') ";
				 }
				 if(CTUtils.isNotEmpty(paramDto.getAsString("state_id"))){
					sqlString += " and t.state='"+paramDto.getAsString("state_id")+"' ";
				 }
				 if(CTUtils.isNotEmpty(paramDto.getAsString("buyer_phone"))){
					sqlString += " and t.buyer_phone like '%"+paramDto.getAsString("buyer_phone")+"%' ";
				 }
				 if(CTUtils.isNotEmpty(paramDto.getAsString("buyer_name"))){
					sqlString += " and t.buyer_name like '%"+paramDto.getAsString("buyer_name")+"%' ";
				 }
				 if(CTUtils.isNotEmpty(paramDto.getAsString("good_name"))){
					sqlString += " and t2.good_name like '%"+paramDto.getAsString("good_name")+"%' ";
				 }
				 if(CTUtils.isNotEmpty(paramDto.getAsString("account"))){
					sqlString += " and ( t5.account like '%"+paramDto.getAsString("account")+"%' or t5.name like '%"+paramDto.getAsString("account")+"%') ";
				 }
				 if(CTUtils.isNotEmpty(paramDto.getAsString("start_date"))){
	            	sqlString += " and t.order_time >= '"+paramDto.getAsString("start_date")+" 00:00:00'";
	             }
	             if(CTUtils.isNotEmpty(paramDto.getAsString("end_date"))){
	         	    sqlString += " and t.order_time <= '"+paramDto.getAsString("end_date")+" 23:59:59'";
	             }

			 sqlString += " order by t.order_time desc";
			 StringUtil.xprint("sql="+sqlString);
			 
			 String countSql = "select count(tt.order_id) as zs, sum(tt.sku_cost*tt.buy_num) as cb,sum(tt.sku_price*tt.buy_num) as jg,sum(tt.sku_pmoney*tt.buy_num) as fc from ( ";
			 countSql+=sqlString+")tt where 1=1 ";
			 StringUtil.xprint("countSql="+countSql);
			 List<Dto> tableList = dbManager.queryForList(countSql);
			 if(!StringUtil.checkListBlank(tableList)){
				 countDto = tableList.get(0);
			 }
			 
		} catch (Exception e) {
			e.printStackTrace();
		}
		return countDto;
	}
	
	
	/**
	 * 获取订单结算流水
	 * @return
	 */
	public  static PageModel getOrderCountList(Dto paramDto) {
		List<Dto> tableList = null;
		DBManager dbManager = new DBManager();
		 try {
			 
			 int pageSize = 0;
			if(CTUtils.isEmpty(paramDto.getAsInteger("pageSize"))){
				pageSize = CTConstants.PAGER_MSIZE;
			}else{
				pageSize = paramDto.getAsInteger("pageSize");
			}
			 int start = 0;
			 if(CTUtils.isNotEmpty(paramDto.getAsInteger("pager.offset"))){
				start = paramDto.getAsInteger("pager.offset");
			 }
			 String sqlString ="select t1.order_id,t1. order_time,t1. buy_num,t1. good_id,t. state_id,"+
		       "(select t2. good_name from wxb_good t2 where t2. good_id = t1.good_id) as good_name,"+
		       "(select t3. channel_name from wxb_channel t3 where t3. channel_id = t1.channel_id) as channel_name,"+
		       "(select t5. sku_pmoney*t1. buy_num from wxb_good_sku t5 where t5.sku_id = t1.sku_id) as sku_pmoney"+
			   " from wxb_order_count t, wxb_order t1"+
			   " where t. order_id = t1. order_id"+
			   " and t1.state <> 8 and t1.user_id='"+paramDto.getAsString("user_id")+"'"+
		       " and t. state_id = '"+paramDto.getAsString("state")+"'";
		   
			 sqlString+=" order by t.createtime desc";
			 //处理分页
			 int totalCount = dbManager.getTotalCount(sqlString);
			 sqlString+=" limit "+start+","+pageSize;
			 StringUtil.xprint("sql="+sqlString);
			 tableList = dbManager.queryForList(sqlString);
			 if(!StringUtil.checkListBlank(tableList)){
				 for (Dto dto : tableList) {
					dto.put("state_name", CTUtils.getCodeName("ORDER_CSTATE", dto.getAsString("state_id")));
				}
			 }
			 
			pager = dbManager.getPageModel(tableList, start,pageSize, totalCount);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return pager;

	}
	
	
	/**
	 * 获取商户订单结算流水
	 * @return
	 */
	public  static PageModel getCustOrderCountList(Dto paramDto) {
		List<Dto> tableList = null;
		DBManager dbManager = new DBManager();
		 try {
			 
			 int pageSize = 0;
			if(CTUtils.isEmpty(paramDto.getAsInteger("pageSize"))){
				pageSize = CTConstants.PAGER_MSIZE;
			}else{
				pageSize = paramDto.getAsInteger("pageSize");
			}
			int start = 0;
			if(CTUtils.isNotEmpty(paramDto.getAsInteger("pager.offset"))){
				start = paramDto.getAsInteger("pager.offset");
			} 
			 String sqlString ="select * from ( select t.*, "+
				 " t1.order_time, "+
				 " t1.buy_num, "+
				 " t1.good_id, "+
				 " t1.state, "+
				 " t1.user_id, "+
				 " (select t5. account "+
				 "  from wxb_memeber t5 "+
				 " where t5.memeber_id = t1.user_id) as account, "+
				 " (select t4. name from wxb_memeber t4 where t4.memeber_id = t1.user_id) as user_name, "+
				 " (select t2. good_name from wxb_good t2 where t2. good_id = t1.good_id) as good_name, "+
				 " (select t6. customer_id from wxb_good t6 where t6. good_id = t1.good_id) as customer_id,"+
				 " (select t3. channel_name "+
				 " from wxb_channel t3 "+
				 " where t3. channel_id = t1.channel_id) as channel_name, "+
				 " (select t5. sku_pmoney "+
				 " from wxb_good_sku t5 "+
				 " where t5.sku_id = t1.sku_id) as sku_pmoney "+
				 " from wxb_order_count t, wxb_order t1 "+
				 " where t. order_id = t1. order_id ";
				 if(CTUtils.isNotEmpty(paramDto.getAsString("c_state"))){
					sqlString += " and t.state_id='"+paramDto.getAsString("c_state")+"' ";
				 }
			     if(CTUtils.isNotEmpty(paramDto.getAsString("o_state"))){
					sqlString += " and t1.state='"+paramDto.getAsString("o_state")+"' ";
				 }
			     if(CTUtils.isNotEmpty(paramDto.getAsString("ocid"))){
					sqlString += " and t.ocid in('"+paramDto.getAsString("ocid")+"') ";
				 }
			     
			 sqlString += " order by t1.order_time desc ) tt where 1=1"; 
			 if(CTUtils.isNotEmpty(paramDto.getAsString("customer_id"))){
				sqlString += " and tt.customer_id='"+paramDto.getAsString("customer_id")+"' ";
			  }
			 if(CTUtils.isNotEmpty(paramDto.getAsString("alisaname"))){
				sqlString += " and (tt.user_name like '%"+paramDto.getAsString("alisaname")+"%' ";
				sqlString += " or tt.account like '%"+paramDto.getAsString("alisaname")+"%' ";
				sqlString += " or tt.channel_name like '%"+paramDto.getAsString("alisaname")+"%' ";
				sqlString += " or tt.good_name like '%"+paramDto.getAsString("alisaname")+"%' ) ";
			 }
			 //处理分页
			 int totalCount = dbManager.getTotalCount(sqlString);
			 sqlString+=" limit "+start+","+pageSize;
			 
			 StringUtil.xprint("sql="+sqlString);
			 tableList = dbManager.queryForList(sqlString);
			 if(!StringUtil.checkListBlank(tableList)){
				 int period = 0;
				 String createtime = null;
				//处理结算日期
				int exp_date = Integer.valueOf(CTUtils.getParamValue("ALIPAY_DATE_PERIOD"));
				StringUtil.xprint("系统结算周期："+exp_date);
				 
				 for (Dto dto : tableList) {
					dto.put("cstate_name", CTUtils.getCodeName("ORDER_CSTATE", dto.getAsString("state_id")));
					dto.put("ostate_name", CTUtils.getCodeName("ORDER_STATE", dto.getAsString("state")));
					
					createtime = dto.getAsString("createtime");
					period = CTUtils.getSubDay(createtime, CTUtils.getCurrentTime());
					if(period>=exp_date){
						dto.put("pay_state","1");
					}else{
						dto.put("pay_state","0");
					}
				}
			 }
			 
			pager = dbManager.getPageModel(tableList, start,pageSize, totalCount);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return pager;

	}
	
	/**
	 * 获取商户财务结算单
	 * @return
	 */
	public  static PageModel getCustOrderMoneyList(Dto paramDto) {
		List<Dto> tableList = null;
		DBManager dbManager = new DBManager();
		 try {
			 
			 int pageSize = 0;
			if(CTUtils.isEmpty(paramDto.getAsInteger("pageSize"))){
				pageSize = CTConstants.PAGER_MSIZE;
			}else{
				pageSize = paramDto.getAsInteger("pageSize");
			}
			int start = 0;
			if(CTUtils.isNotEmpty(paramDto.getAsInteger("pager.offset"))){
				start = paramDto.getAsInteger("pager.offset");
			}
			String sqlString ="select * from ( " +
								 " select t.*, " +
								 " t1.name, " +
								 " t1. account, " +
								 " t2.createtime as count_time,"+
								 " (select t4. customer_id " +
								 " from wxb_good t4 " +
								 "  where t4. good_id = t3.good_id) as customer_id, " +
								 " TIMESTAMPDIFF(DAY, now(), t2.exp_pay_date) as exp_day, " +
								 "  t1. pay_account " +
								 "  from wxb_user_money t, wxb_memeber t1, wxb_order_count t2, wxb_order t3 " +
								 " where t. user_id = t1. memeber_id " +
								 "  and t.ocid = t2.ocid " +
								 "  and t2. order_id = t3. order_id ";
			 
			 sqlString += " order by t.createtime desc,t.user_id asc ) tt where 1=1"; 
			 if(CTUtils.isNotEmpty(paramDto.getAsString("customer_id"))){
				sqlString += " and tt.customer_id='"+paramDto.getAsString("customer_id")+"' ";
			 }
			 if(CTUtils.isNotEmpty(paramDto.getAsString("stateid"))){
				sqlString += " and tt.state='"+paramDto.getAsString("stateid")+"' ";
			 }
			 if(CTUtils.isNotEmpty(paramDto.getAsString("um_id"))){
				sqlString += " and tt.um_id in ('"+paramDto.getAsString("um_id")+"') ";
			 }
			 if(CTUtils.isNotEmpty(paramDto.getAsString("alisaname"))){
					sqlString += " and (tt.name like '%"+paramDto.getAsString("alisaname")+"%' ";
					sqlString += " or tt.account like '%"+paramDto.getAsString("alisaname")+"%' ";
					sqlString += " or tt.pay_account like '%"+paramDto.getAsString("alisaname")+"%' ) ";
			 }
			 //处理分页
			 int totalCount = dbManager.getTotalCount(sqlString);
			 sqlString+=" limit "+start+","+pageSize;
			 StringUtil.xprint("sql="+sqlString);
			 //处理结算日期
			 int exp_date = Integer.valueOf(CTUtils.getParamValue("ALIPAY_DATE_PERIOD"));
			 StringUtil.xprint("系统结算周期："+exp_date);
			 tableList = dbManager.queryForList(sqlString);
			 if(!StringUtil.checkListBlank(tableList)){
				 int period = 0;
				 String createtime = null;
				 for (Dto dto : tableList) {
					createtime = dto.getAsString("count_time");
					period = CTUtils.getSubDay(createtime, CTUtils.getCurrentTime());
					if(period>=exp_date){
						dto.put("pay_state","1");
					}else{
						dto.put("pay_state","0");
					}
					dto.put("state_name", CTUtils.getCodeName("PAYSATE", dto.getAsString("state")));
				}
			 }
			 
			pager = dbManager.getPageModel(tableList, start,pageSize, totalCount);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return pager;

	}
	
	/**
	 * 更新商品信息
	 * @param good_id
	 * @param vnum
	 * @return
	 */
	public static boolean updateGoodVisitNum(String good_id,int vnum) {
		boolean temp = false;
		DBManager dbManager = new DBManager();
		try {
			if(CTUtils.isNotEmpty(good_id)){
				String sqlString = " update wxb_good t set t.visit_num='"+vnum+"' where t.good_id='"+good_id+"'";
				temp = dbManager.execute(sqlString);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return temp;
	}
	
	
	public static boolean addOrderMoney(Dto paramDto){
		boolean temp = false;
		String sqlString = null;
		DBManager dbManager = new DBManager();
		try {
			
			sqlString = "insert into wxb_user_money(user_id,order_money,visit_money,own_money,state,createtime,ocid)values(";
			if(StringUtil.isNotEmpty(paramDto.getAsString("user_id"))){
				sqlString +=" '"+paramDto.getAsString("user_id")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("order_money"))){
				sqlString +=" '"+paramDto.getAsString("order_money")+"',";
			}else{
				sqlString +=" 0,";
			}
			
			if(StringUtil.isNotEmpty(paramDto.getAsString("visit_money"))){
				sqlString +=" '"+paramDto.getAsString("visit_money")+"',";
			}else{
				sqlString +=" 0,";
			}
			
			if(StringUtil.isNotEmpty(paramDto.getAsString("own_money"))){
				sqlString +=" '"+paramDto.getAsString("own_money")+"',";
			}else{
				sqlString +=" 0,";
			}
			
			if(StringUtil.isNotEmpty(paramDto.getAsString("state"))){
				sqlString +=" '"+paramDto.getAsString("state")+"',";
			}
			
			if(StringUtil.isNotEmpty(paramDto.getAsString("createtime"))){
				sqlString +=" '"+paramDto.getAsString("createtime")+"',";
			}
			
			if(StringUtil.isNotEmpty(paramDto.getAsString("ocid"))){
				sqlString +=" '"+paramDto.getAsString("ocid")+"')";
			}
			
			temp = dbManager.insertBySQL(sqlString);
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			//处理记录日志
			Dto logDto =new BaseDto();
			logDto.put("otype", "INSERT");
			logDto.put("msql_text", sqlString);
			logDto.put("createtime", CTUtils.getCurrentTime());
			logDto.put("state", temp==true?"0":"1");
			addMsSqlLog(logDto); 
		}
		
		return temp;
		
	}
	
	public static boolean addOrderCount(Dto paramDto){
		boolean temp = false;
		String sqlString = null;
		DBManager dbManager = new DBManager();
		try {
			sqlString = "insert into wxb_order_count(ocid,order_id,order_money,order_pmoney,state_id,createtime,remark,exp_pay_date)values(";
			
			if(StringUtil.isNotEmpty(paramDto.getAsString("ocid"))){
				sqlString +=" '"+paramDto.getAsString("ocid")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("order_id"))){
				sqlString +=" '"+paramDto.getAsString("order_id")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("order_money"))){
				sqlString +=" '"+paramDto.getAsString("order_money")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("order_pmoney"))){
				sqlString +=" '"+paramDto.getAsString("order_pmoney")+"',";
			}
			if(StringUtil.isNotEmpty(paramDto.getAsString("state_id"))){
				sqlString +=" '"+paramDto.getAsString("state_id")+"',";
			}
			
			if(StringUtil.isNotEmpty(paramDto.getAsString("createtime"))){
				sqlString +=" '"+paramDto.getAsString("createtime")+"',";
			}
			
			if(StringUtil.isNotEmpty(paramDto.getAsString("remark"))){
				sqlString +=" '"+paramDto.getAsString("remark")+"',";
			}else{
				sqlString +=" null)";
			}
			
			if(StringUtil.isNotEmpty(paramDto.getAsString("exp_pay_date"))){
				sqlString +=" '"+paramDto.getAsString("exp_pay_date")+"')";
			}else{
				sqlString +=" null)";
			}
			
			temp = dbManager.insertBySQL(sqlString);
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			//处理记录日志
			Dto logDto =new BaseDto();
			logDto.put("otype", "INSERT");
			logDto.put("msql_text", sqlString);
			logDto.put("createtime", CTUtils.getCurrentTime());
			logDto.put("state", temp==true?"0":"1");
			addMsSqlLog(logDto); 
		}
		
		return temp;
		
	}
	
	/**
	 * 获取订单对应的会员
	 * @param order_id
	 */
	public static Dto getORMemeberByOrderID(String order_id) {
		Dto memberDto = null;
		DBManager dbManager = new DBManager();
		try {
			String sqlString = " select * from wxb_memeber tt "+
				" where tt.visit_code = (select t2. recom_user "+
				" from wxb_order t, wxb_memeber t2 "+
				" where t. user_id = t2. memeber_id "+
				" and t.order_id in ('"+order_id+"'))";
			List<Dto> cList = dbManager.queryForList(sqlString);
			StringUtil.xprint("sql="+sqlString);
			if(!StringUtil.checkListBlank(cList)){
				memberDto = cList.get(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return memberDto;
	}
	
	static 	BonusX bonusImp=new BonusX();
	/**
	 * 修改订单流水状态
	 * @param state_id
	 * @param ocid
	 * @return
	 */
	public static boolean updateOrderCount(String state_id,String ocid) {
		boolean temp = false;
		DBManager dbManager = new DBManager();
		try {
			// 获取订单流水集合信息
			Dto pDto = new BaseDto();
			Dto orMemberInfo = null;
			List<Dto> ocList = null;
			if (CTUtils.isNotEmpty(ocid)) {
				ocid = ocid.replaceAll(",", "','");
				pDto.put("ocid", ocid);
				if (state_id.equalsIgnoreCase("3")) {// 处理生成打款单据
					ocList = getCustOrderCountList(pDto).getDatas();
					if (!StringUtil.checkListBlank(ocList)) {
						double bonesPercent=bonusImp.get_bonusPercent();
						Dto orDto = null;
						Dto omDto = null;
						for (Dto oDto : ocList) {
							//double orderMoney = Double.valueOf(oDto.getAsString("order_money"));//订单金额
							double orderPMoney = Double.valueOf(oDto.getAsString("order_pmoney"));//分成
							orMemberInfo = (BaseDto) getORMemeberByOrderID(oDto.getAsString("order_id"));
							// 如果此订单有上级推荐人 则增加一条邀请奖励金额
							if (CTUtils.isNotEmpty(orMemberInfo)) {

								orDto = new BaseDto();
								orDto.put("user_id", orMemberInfo.getAsString("memeber_id"));// 推荐人ID
								orDto.put("visit_money",orderPMoney * bonesPercent);// 计算奖励金额
								orDto.put("own_money",orderPMoney * bonesPercent);// 应返款数
								orDto.put("order_money","0.0");
								orDto.put("state", "0");// 未支付
								orDto.put("ocid", oDto.getAsString("ocid"));// 流水号
								orDto.put("createtime", CTUtils.getCurrentTime());// 返款时间
								// 增加记录
								addOrderMoney(orDto);

							}

							// 生成订单返款单
							omDto = new BaseDto();
							omDto.put("user_id", oDto.getAsString("user_id"));// 当前用户编号
							omDto.put("order_money", orderPMoney);// 订单金额
							omDto.put("own_money", orderPMoney);// 应返款数
							omDto.put("visit_money", "0.0");
							omDto.put("state", "0");// 未支付
							omDto.put("ocid", oDto.getAsString("ocid"));// 流水号
							omDto.put("createtime", CTUtils.getCurrentTime());// 返款时间
							// 增加记录
							addOrderMoney(omDto);
						}
					}
				}
			}

			//处理订单流水状态
			temp = updateOrderCountSate(state_id, ocid);
		} catch (Exception e) {
			temp = false;
			e.printStackTrace();
		}
		return temp;
	}
	
	/**
	 * 修改订单结算状态
	 * @param state_id
	 * @param um_id
	 * @return
	 */
	public static boolean updateOrderMoney(String state_id,String um_id) {
		boolean temp = false;
		String sqlString = null;
		DBManager dbManager = new DBManager();
		try {
			if(CTUtils.isNotEmpty(state_id)){
				sqlString = "  update wxb_user_money t set t.state='"+state_id+"' where t.um_id in("+um_id+")";
				temp = dbManager.execute(sqlString);
				StringUtil.xprint("sql="+sqlString);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			//处理记录日志
			Dto logDto =new BaseDto();
			logDto.put("otype", "UPDATE");
			logDto.put("msql_text", sqlString);
			logDto.put("createtime", CTUtils.getCurrentTime());
			logDto.put("state", temp==true?"0":"1");
			addMsSqlLog(logDto); 
		}
		return temp;
	}
	
	/**
	 * 修改订单流水状态
	 * @param state_id
	 * @param um_id
	 * @return
	 */
	public static boolean updateOrderCountSate(String state_id,String oc_id) {
		boolean temp = false;
		String sqlString = null;
		DBManager dbManager = new DBManager();
		try {
			if(CTUtils.isNotEmpty(state_id)){
				sqlString = "  update wxb_order_count t set t.state_id='"+state_id+"' where t.ocid in ('"+oc_id+"')";
				temp = dbManager.execute(sqlString);
				StringUtil.xprint("sql="+sqlString);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			//处理记录日志
			Dto logDto =new BaseDto();
			logDto.put("otype", "UPDATE");
			logDto.put("msql_text", sqlString);
			logDto.put("createtime", CTUtils.getCurrentTime());
			logDto.put("state", temp==true?"0":"1");
			addMsSqlLog(logDto); 
		}
		return temp;
	}
	
	
	/**
	 * 修改订单信息
	 * @param state_id
	 * @param um_id
	 * @return
	 */
	public static boolean updateOrderCountSate(Dto paramDto) {
		boolean temp = false;
		String sqlString = null;
		DBManager dbManager = new DBManager();
		try {
			if(CTUtils.isNotEmpty(paramDto)){
				sqlString = " update wxb_order t set ";
				
				if(CTUtils.isNotEmpty(paramDto.getAsString("sender_type"))){
	            	sqlString+=" t.sender_type='"+paramDto.getAsString("sender_type")+"',";
				}
				if(CTUtils.isNotEmpty(paramDto.getAsString("courier_id"))){
	            	sqlString+=" t.courier_id='"+paramDto.getAsString("courier_id")+"',";
				}
				if(CTUtils.isNotEmpty(paramDto.getAsString("order_remark"))){
	            	sqlString+=" t.order_remark='"+paramDto.getAsString("order_remark")+"',";
				}
				if(CTUtils.isNotEmpty(paramDto.getAsString("state"))){
	            	sqlString+=" t.state='"+paramDto.getAsString("state")+"' ";
				}
				
				sqlString+=" where t.order_id = '"+paramDto.getAsString("order_id")+"' ";
				temp = dbManager.execute(sqlString);
				StringUtil.xprint("sql="+sqlString);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			//处理记录日志
			Dto logDto =new BaseDto();
			logDto.put("otype", "UPDATE");
			logDto.put("msql_text", sqlString);
			logDto.put("createtime", CTUtils.getCurrentTime());
			logDto.put("state", temp==true?"0":"1");
			addMsSqlLog(logDto); 
		}
		return temp;
	}
	
	/**
	 * 增加用户访问日志
	 * @param paramDto
	 * @return
	 */
	public static boolean addVisitBuyLog(Dto paramDto) {
		boolean temp = false;
		DBManager dbManager = new DBManager();
		try {
			String sqlString = "insert into wxb_visit_log(good_id,user_id,channel_id,visit_ip,order_id,visit_num,visit_time)values(";
			
			if(CTUtils.isNotEmpty(paramDto.getAsString("good_id"))){
				sqlString+="'"+paramDto.getAsString("good_id")+"',";
			}
			
            if(CTUtils.isNotEmpty(paramDto.getAsString("user_id"))){
            	sqlString+="'"+paramDto.getAsString("user_id")+"',";
			}
            
            if(CTUtils.isNotEmpty(paramDto.getAsString("channel_id"))){
            	sqlString+="'"+paramDto.getAsString("channel_id")+"',";
			}else{
				sqlString+="null,";
			}
            
            if(CTUtils.isNotEmpty(paramDto.getAsString("visit_ip"))){
            	sqlString+="'"+paramDto.getAsString("visit_ip")+"',";
			}else{
				sqlString+="null,";
			}
            
            if(CTUtils.isNotEmpty(paramDto.getAsString("order_id"))){
            	sqlString+="'"+paramDto.getAsString("order_id")+"',";
			}else{
				sqlString+="null,";
			}
            
            if(CTUtils.isNotEmpty(paramDto.getAsString("visit_num"))){
            	sqlString+="'"+paramDto.getAsString("visit_num")+"',";
			}
            
            sqlString+="'"+CTUtils.getCurrentTime()+"')";
			StringUtil.xprint("sql="+sqlString);
			temp = dbManager.execute(sqlString);
			
			
		} catch (Exception e) {
			e.printStackTrace();
			StringUtil.xprint("增加用户访问日志失败");
		}
		return temp;
	}
	
	/**
	 * 获取订单结算与返款列表
	 * @return
	 */
	public  static PageModel getOrderPayMoneyList(Dto paramDto) {
		List<Dto> tableList = null;
		DBManager dbManager = new DBManager();
		try {
			int pageSize = 0;
			if(CTUtils.isEmpty(paramDto.getAsInteger("pageSize"))){
				pageSize = CTConstants.PAGER_MSIZE;
			}else{
				pageSize = paramDto.getAsInteger("pageSize");
			}
			int start = 0;
			if(CTUtils.isNotEmpty(paramDto.getAsInteger("pager.offset"))){
				start = paramDto.getAsInteger("pager.offset");
			} 
			String sqlString = " select * from wxb_user_money t where t.user_id = '"+paramDto.getAsString("user_id")+"' order by t.createtime desc";
			//处理分页
			int totalCount = dbManager.getTotalCount(sqlString);
			 sqlString+=" limit "+start+","+pageSize;
			 StringUtil.xprint("sql="+sqlString);
			 tableList = dbManager.queryForList(sqlString);
			 if(!StringUtil.checkListBlank(tableList)){
				 for (Dto dto : tableList) {
					dto.put("state_name", CTUtils.getCodeName("PAYSATE", dto.getAsString("state")));
				}
			 }
			 
			pager = dbManager.getPageModel(tableList, start,pageSize, totalCount);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return pager;
	}
	
	
	/**
	 * 获取订单结算与返款列表
	 * @return
	 */
	public  static PageModel getCustomPayLog(Dto paramDto) {
		List<Dto> tableList = null;
		DBManager dbManager = new DBManager();
		try {
			int pageSize = 0;
			if(CTUtils.isEmpty(paramDto.getAsInteger("pageSize"))){
				pageSize = CTConstants.PAGER_MSIZE;
			}else{
				pageSize = paramDto.getAsInteger("pageSize");
			}
			int start = 0;
			if(CTUtils.isNotEmpty(paramDto.getAsInteger("pager.offset"))){
				start = paramDto.getAsInteger("pager.offset");
			} 
			String sqlString = " select * from wxb_pay_log t where t.customer_id = '"+
			                    paramDto.getAsString("customer_id")+"' ";
			
			if(CTUtils.isNotEmpty(paramDto.getAsString("alisaname"))){
				sqlString += " and (t.alipay_code like '%"+paramDto.getAsString("alisaname")+"%' ";
				sqlString += " or t.account_name like '%"+paramDto.getAsString("alisaname")+"%' ";
				sqlString += " or t.pay_account like '%"+paramDto.getAsString("alisaname")+"%' ) ";
		    }
			//处理分页
			int totalCount = dbManager.getTotalCount(sqlString);
			sqlString+=" order by t.createtime desc limit "+start+","+pageSize;
			StringUtil.xprint("sql="+sqlString);
			tableList = dbManager.queryForList(sqlString);
			pager = dbManager.getPageModel(tableList, start,pageSize, totalCount);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return pager;
	}
	
	/**
	 * 获取商户信息
	 * @return
	 */
	public static List<Dto> getCustomerList() {
		List<Dto> customerList = null;
		 try {
			 
			 customerList = getTableList("wxb_customer", " t.state=1");

		} catch (Exception e) {
			e.printStackTrace();
		}
		return customerList;

	}
	
	/**
	 * 获取所有渠道并加密参数
	 * @param good_id
	 * @param user_id
	 * @return
	 */
	public static List<Dto> getChannelList(String good_id,String user_id) {
		List<Dto> chList = null;
		DBManager dbManager = new DBManager();
		try {
			String sqlString = "select * from wxb_channel t where t.channel_uid = '"+user_id+"'";
			chList = dbManager.queryForList(sqlString);
			if(!StringUtil.checkListBlank(chList)){
				String ch_id = "";
				String jm_str = "";
				for (Dto dto : chList) {
					ch_id = dto.getAsString("channel_id");
					jm_str = good_id+"-"+user_id+"-"+ch_id;//商品ID-用户ID-渠道ID
					StringUtil.xprint("jm_str=="+jm_str);
					jm_str  = CTUtils.encryptBased64(jm_str);
					//jm_str = URLEncoder.encode(jm_str);
					dto.put("evalue", jm_str);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return chList;
	}
	
	/**
	 * 通过商品获取对应的套餐列表
	 * @param good_id
	 * @return
	 */
	public static List<Dto> getGoodSkuList(String good_id) {
		List<Dto> skuList = null;
		DBManager dbManager = new DBManager();
		try {
			String sql = "select * from wxb_good_sku t where t.good_id='"+good_id+"' order by t.order_no asc";
			skuList = dbManager.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return skuList;
	}
	
	/**
	 * 通过套餐ID获取信息
	 * @param skuid
	 * @return
	 */
	public static Dto getGoodSkuByID(String skuid) {
		Dto skuInfo = null;
		DBManager dbManager = new DBManager();
		try {
			String sql = "select * from wxb_good_sku t where t.sku_id='"+skuid+"'";
			skuInfo = dbManager.queryForList(sql).get(0);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return skuInfo;
	}
	
	/**
	 * 通过ID获取商品
	 * @param good_id
	 * @return
	 */
	public static Dto getGoodByID(String good_id) {
		Dto goodInfo = null;
		DBManager dbManager = new DBManager();
		try {
			String sqlString = "select t.*,t1.customer_name,t1.QQ,t1.wxh from wxb_good t,wxb_customer t1 where t.customer_id = t1.customer_id and t.good_id='"+good_id+"'";
			goodInfo = dbManager.queryForList(sqlString).get(0);
			if(CTUtils.isNotEmpty(goodInfo)){
				
				String sku_title = goodInfo.getAsString("sku_title");
				String sku_price = goodInfo.getAsString("sku_price");
				String sku_pmoney = goodInfo.getAsString("sku_pmoney");
				
				String sku_titles[];
				String sku_prices[];
				String sku_pmoneys[];

				int len;
				List<Dto> copyList = null;
				StringBuffer sBuffer = new StringBuffer("");
				if(StringUtil.isNotEmpty(sku_title) && StringUtil.isNotEmpty(sku_price)){
				    sku_titles = sku_title.split("\\|");
				    sku_prices = sku_price.split("\\|");
				    sku_pmoneys = sku_pmoney.split("\\|");
				    len = sku_titles.length;
				    sBuffer.append("<tr>");
				    for (int i = 0; i < len; i++) {
					    sBuffer.append("<tr>");
				    	sBuffer.append("<td>"+sku_titles[i]+"</td>");
				    	sBuffer.append("<td class=\"text-yellow\">"+sku_prices[i]+"</td>");
				    	sBuffer.append("<td class=\"text-green\">"+sku_pmoneys[i]+"</td>");
				    	sBuffer.append("</tr>");
					}

				}
				goodInfo.put("good_sku", sBuffer.toString());
				
				
				//获取微信文案列表
				if(StringUtil.isNotEmpty(goodInfo.getAsString("copy_ids"))){
					copyList = getTableList("wxb_good_copy", " t.copy_id in("+goodInfo.getAsString("copy_ids")+")");
					goodInfo.put("copyList", copyList);
				}
				
				//获取非微信文案列表
				if(StringUtil.isNotEmpty(goodInfo.getAsString("spc_id"))){
					copyList = getTableList("wxb_good_copy", " t.copy_id in("+goodInfo.getAsString("spc_id")+")");
					goodInfo.put("spcList", copyList);
					if(CTUtils.isNotEmpty(copyList)){
					   goodInfo.put("spcListSize", copyList.size());
					}else {
					   goodInfo.put("spcListSize", "0");
					}
				}
				
				//获取空间文案列表
				if(StringUtil.isNotEmpty(goodInfo.getAsString("zon_id"))){
					copyList = getTableList("wxb_good_copy", " t.copy_id in("+goodInfo.getAsString("zon_id")+")");
					goodInfo.put("zonList", copyList);
					if(CTUtils.isNotEmpty(copyList)){
					   goodInfo.put("zonListSize", copyList.size());
					}else {
					   goodInfo.put("zonListSize", "0");
					}
				}
				
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return goodInfo;
	}
	
	
	//验证会员账户是否已经存在
	public static boolean checkGoodName(String good_name,String customer_id) {
		boolean bool = false;
		DBManager dbManager = new DBManager();
		try {
			String sql = "select * from wxb_good t where 1=1 ";
			if(StringUtil.isNotEmpty(good_name)){
				sql+=" and t.good_name = '"+good_name+"' ";
			}
			if(StringUtil.isNotEmpty(customer_id)){
				sql+=" and t.customer_id = '"+customer_id+"' ";
			}
			
			StringUtil.xprint("querySQL="+sql);
			List<Dto> cList = dbManager.queryForList(sql);
			if(!StringUtil.checkListBlank(cList)){
				bool = true;
			}else{
				bool = false;
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return bool;
	}
	
	/**
	 * 按商品统计
	 * @param paraDto
	 * @return
	 */
	public static Dto getProductCountList(Dto paraDto) {
		Dto proDto = null;
		DBManager dbManager = new DBManager();
		try {
			String sqlString = "select tt.good_id,tt.customer_id,"+
						       "tt.good_name as gname,"+
						       //"tt.visit_num as fws,"+
						       "count(tt.zdd) as zdds,"+
						       "count(tt.yqs) as yxds,"+
						       "count(tt.dfh) as dfhs,"+
						       "count(tt.yqs) / count(tt.zdd) as cdl,"+
						       "count(tt.yqx) as yqxs,"+
						       "count(tt.yfh) as yfhs,"+
						       "count(tt.dqs) as dqss,"+
						       "count(tt.yjs) as yjss,"+
						       "count(tt.yjs) / count(tt.zdd) as jsl,"+
						       "count(tt.yqs) as yqss,"+
						       "count(tt.yqs) / count(tt.zdd) as qsl,"+
						       "count(tt.yth) as yths,"+
						       "count(tt.yth) / count(tt.zdd) as thl "+
						  "from (select o.good_id,o.good_name,o.customer_id,t.order_time," +
						   //"    (select count(r.visit_num) from wxb_visit_log r where r.good_id = t.good_id and r.user_id = t.user_id) as visit_num," +
						   "              t.user_id, "+
						   "            (select b.order_id "+
						   "               from wxb_order b "+
						   "              where b.order_id = t.order_id) as zdd,"+
						   "            (select b0.order_id "+
						   "              from wxb_order b0 "+
						   "              where b0.order_id = t.order_id "+
						   "                and b0.state = 1) as yxd, "+
						   "            (select b1.order_id "+
						   "               from wxb_order b1  "+
						   "              where b1.order_id = t.order_id "+
						   "                and b1.state = 2) as dfh, "+
						   "            (select b2.order_id "+
						   "               from wxb_order b2 "+
						   "              where b2.order_id = t.order_id "+
						   "                and b2.state = 3) as yqx, "+
						   "            (select b3.order_id "+
						   "              from wxb_order b3 "+
						   "              where b3.order_id = t.order_id "+
						   "                and b3.state = 4) as yfh, "+
						   "            (select b4.order_id "+
						   "               from wxb_order b4 "+
						   "              where b4.order_id = t.order_id "+
						   "                and b4.state = 5) as dqs, "+
						   "            (select b5.order_id "+
						   "               from wxb_order b5 "+
						   "              where b5.order_id = t.order_id "+
						   "                and b5.state = 6) as yjs, "+
						   "            (select b6.order_id "+
						   "               from wxb_order b6 "+
						   "              where b6.order_id = t.order_id "+
						   "                and b6.state = 7) as yqs, "+
						   "            (select b7.order_id "+
						   "               from wxb_order b7 "+
						   "              where b7.order_id = t.order_id "+
						   "                and b7.state = 8) as yth "+
						   "       from wxb_good o left outer join wxb_order t on o.good_id = t.good_id ";
						   if(CTUtils.isNotEmpty(paraDto.getAsString("user_id"))){
					           sqlString += " and t.user_id = '"+paraDto.getAsString("user_id")+"'";
					       }

			               if(CTUtils.isNotEmpty(paraDto.getAsString("start_date"))){
			            	   sqlString += " and t.order_time >= '"+paraDto.getAsString("start_date")+" 00:00:00'";
			               }

			               if(CTUtils.isNotEmpty(paraDto.getAsString("end_date"))){
			            	   sqlString += " and t.order_time <= '"+paraDto.getAsString("end_date")+" 23:59:59'";
			               }
			               
			               sqlString+=" order by t.good_id asc ) tt where 1=1 ";
			               if(CTUtils.isNotEmpty(paraDto.getAsString("customer_id"))){
			            	   sqlString += " and tt.customer_id = '"+paraDto.getAsString("customer_id")+"'";
			               }
			               if(CTUtils.isNotEmpty(paraDto.getAsString("good_name"))){
			            	   sqlString += " and tt.good_name like '%"+paraDto.getAsString("good_name")+"%'";
			               }
			               
						   sqlString += " group by tt.good_id asc ";
						   
			StringUtil.xprint("querySQL="+sqlString);
			List<Dto> datatList = dbManager.queryForList(sqlString);
			//查询合计数据
			String countSql =  "select "+
								//"sum(tm.fws)as zfws, "+
								"sum(tm.zdds)as zs, "+
								"sum(tm.yxds)as yx, "+
								"sum(tm.yqss)/sum(zdds) as zcdl, "+
								"sum(tm.dfhs)as df, "+
								"sum(tm.yqxs)as qx, "+
								"sum(tm.yfhs)as fh, "+
								"sum(tm.dqss)as dq, "+
								"sum(tm.yqss)as qs, "+
								"sum(tm.yqss)/sum(zdds) as zqsl, "+
								"sum(tm.yjss)as js, "+
								"sum(tm.yjss)/sum(zdds) as zjsl, "+
								"sum(tm.yths)as th, "+
								"sum(tm.yths)/sum(zdds) as zthl "+
								"from ( "+sqlString+")tm where 1=1 ";
			List<Dto> countList = dbManager.queryForList(countSql);
			StringUtil.xprint("countSQL="+countSql);
			
			proDto = new BaseDto();
			if(!StringUtil.checkListBlank(countList)){//处理总数
				proDto.put("totalList", countList);
				
			}
			if(!StringUtil.checkListBlank(datatList)){//处理结果集
				proDto.put("datatList", datatList);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return proDto;
	}
	
	
	/**
	 * 按渠道统计
	 * @param paraDto
	 * @return
	 */
	public static Dto getChannelCountList(Dto paraDto) {
		Dto proDto = null;
		DBManager dbManager = new DBManager();
		try {
			String sqlString = "select tt.channel_id,"+
						       "tt.channel_name as cname,"+
						       //"tt.visit_num as fws,"+
						       "sum(tt.pmoney) as fc,"+
						       "count(tt.zdd) as zdds,"+
						       "count(tt.yqs) as yxds,"+
						       "count(tt.dfh) as dfhs,"+
						       "count(tt.yqs) / count(tt.zdd) as cdl,"+
						       "count(tt.yqx) as yqxs,"+
						       "count(tt.yfh) as yfhs,"+
						       "count(tt.dqs) as dqss,"+
						       "count(tt.yjs) as yjss,"+
						       "count(tt.yjs) / count(tt.zdd) as jsl,"+
						       "count(tt.yqs) as yqss,"+
						       "count(tt.yqs) / count(tt.zdd) as qsl,"+
						       "count(tt.yth) as yths,"+
						       "count(tt.yth) / count(tt.zdd) as thl "+
						  "from (select t.channel_id,o.channel_name,t.order_time," +
						   //"    (select count(s.visit_num) from wxb_visit_log s where s.channel_id = t.channel_id and s.user_id = t.user_id) as visit_num," +
						   "    t.user_id, "+
						   "            (select b.order_id "+
						   "               from wxb_order b "+
						   "              where b.order_id = t.order_id) as zdd,"+
						   "            (select b0.order_id "+
						   "              from wxb_order b0 "+
						   "              where b0.order_id = t.order_id "+
						   "                and b0.state = 1) as yxd, "+
						   "            (select b1.order_id "+
						   "               from wxb_order b1  "+
						   "              where b1.order_id = t.order_id "+
						   "                and b1.state = 2) as dfh, "+
						   "            (select b2.order_id "+
						   "               from wxb_order b2 "+
						   "              where b2.order_id = t.order_id "+
						   "                and b2.state = 3) as yqx, "+
						   "            (select b3.order_id "+
						   "              from wxb_order b3 "+
						   "              where b3.order_id = t.order_id "+
						   "                and b3.state = 4) as yfh, "+
						   "            (select b4.order_id "+
						   "               from wxb_order b4 "+
						   "              where b4.order_id = t.order_id "+
						   "                and b4.state = 5) as dqs, "+
						   "            (select b5.order_id "+
						   "               from wxb_order b5 "+
						   "              where b5.order_id = t.order_id "+
						   "                and b5.state = 6) as yjs, "+
						   "            (select b6.order_id "+
						   "               from wxb_order b6 "+
						   "              where b6.order_id = t.order_id "+
						   "                and b6.state = 7) as yqs, "+
						   "            (select b7.order_id "+
						   "               from wxb_order b7 "+
						   "              where b7.order_id = t.order_id "+
						   "                and b7.state = 8) as yth, "+
						   "             t.buy_num * r.sku_pmoney as pmoney"+
						   "       from wxb_order t, wxb_channel o,wxb_good p,wxb_good_sku r " +
						   "       where p.good_id = t.good_id and t.channel_id = o.channel_id " +
						   "       and t.sku_id = r.sku_id "+
						   "      order by t.channel_id asc )tt where 1=1 ";
			
						   if(CTUtils.isNotEmpty(paraDto.getAsString("user_id"))){
				         	   sqlString += " and tt.user_id = '"+paraDto.getAsString("user_id")+"'";
				           }
			               
			               if(CTUtils.isNotEmpty(paraDto.getAsString("channel_name"))){
			            	   sqlString += " and tt.channel_name like '%"+paraDto.getAsString("channel_name")+"%'";
			               }
			               
			               if(CTUtils.isNotEmpty(paraDto.getAsString("start_date"))){
			            	   sqlString += " and tt.order_time >= '"+paraDto.getAsString("start_date")+" 00:00:00'";
			               }

			               if(CTUtils.isNotEmpty(paraDto.getAsString("end_date"))){
			            	   sqlString += " and tt.order_time <= '"+paraDto.getAsString("end_date")+" 23:59:59'";
			               }
						   sqlString += " group by tt.channel_id asc ";
						   
			StringUtil.xprint("querySQL="+sqlString);
			List<Dto> datatList = dbManager.queryForList(sqlString);
			//查询合计数据
			String countSql =  "select "+
								//"sum(tm.fws)as zfws, "+
								"sum(tm.zdds)as zs, "+
								"sum(tm.fc)as zfc, "+
								"sum(tm.yxds)as yx, "+
								"sum(tm.yqss)/sum(zdds) as zcdl, "+
								"sum(tm.dfhs)as df, "+
								"sum(tm.yqxs)as qx, "+
								"sum(tm.yfhs)as fh, "+
								"sum(tm.dqss)as dq, "+
								"sum(tm.yqss)as qs, "+
								"sum(tm.yqss)/sum(zdds) as zqsl, "+
								"sum(tm.yjss)as js, "+
								"sum(tm.yjss)/sum(zdds) as zjsl, "+
								"sum(tm.yths)as th, "+
								"sum(tm.yths)/sum(zdds) as zthl "+
								"from ( "+sqlString+")tm where 1=1 ";
			List<Dto> countList = dbManager.queryForList(countSql);
			StringUtil.xprint("countSQL="+countSql);
			
			proDto = new BaseDto();
			if(!StringUtil.checkListBlank(countList)){//处理总数
				proDto.put("totalList", countList);
				
			}
			if(!StringUtil.checkListBlank(datatList)){//处理结果集
				proDto.put("datatList", datatList);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return proDto;
	}

	
	/**
	 * 获取商品列表
	 * @param paramDto 参数
	 */
	public static PageModel getGoodList(Dto paramDto) {
		List<Dto> goodList = null;
		List<Dto> goodList1 = null;
		DBManager dbManager = new DBManager();
		try {
			String sql = "select t.*,t1.customer_name,t.state,t1.level from wxb_good t,wxb_customer t1 where t.customer_id=t1.customer_id ";
			String type_id = paramDto.getAsString("type_id");
			String tag_id = paramDto.getAsString("tag_id");
			String customer_id = paramDto.getAsString("customer_id");
			String alisaname = paramDto.getAsString("alisaname");
			String enabled = paramDto.getAsString("enabled");
			String sort_type = paramDto.getAsString("sort_type");
			int pageSize = 0;
			if(CTUtils.isEmpty(paramDto.getAsInteger("pageSize"))){
				pageSize = CTConstants.PAGER_MSIZE;
			}else{
				pageSize = paramDto.getAsInteger("pageSize");
			}
			int start = 0;
			if(CTUtils.isNotEmpty(paramDto.getAsInteger("pager.offset"))){
				start = paramDto.getAsInteger("pager.offset");
			}
			
			if(CTUtils.isNotEmpty(tag_id)){//添加标签过滤
				sql+=" and t.tags like '%"+tag_id+"%' ";
			}
			
			if(CTUtils.isNotEmpty(customer_id)){//添加商户过滤
				sql+=" and t.customer_id = '"+customer_id+"' ";
			}
			
			if(CTUtils.isNotEmpty(type_id)){//添加类型过滤
				sql+=" and t.type_id = '"+type_id+"' ";
			}
			if(CTUtils.isNotEmpty(enabled)){//是否有效
				sql+=" and t.state = '"+enabled+"' and t1.state ='"+enabled+"'";
			}
			
			if(CTUtils.isNotEmpty(alisaname)){//添加类型过滤
				sql+=" and (t.good_name like '%"+alisaname+"%' or t.promote_desc like '%"+alisaname+"%') ";
			}
			
			if(CTUtils.isEmpty(sort_type)){
				sql+=" order by t.toped_time desc,t.create_time desc";
			}
			
			if(CTUtils.isNotEmpty(sort_type)){
				if(sort_type.equals("toped")){
				   sql+=" order by t.toped_time desc,t.create_time desc";
				}
				if(sort_type.equals("recommed")){
				   sql+=" order by t.recomed_time desc,t.create_time desc";
			    }
				if(sort_type.equals("level")){
			       sql+=" order by t1.level desc,t.create_time desc";
				}
			}
			
			//处理分页
			int totalCount = dbManager.getTotalCount(sql);
			sql+=" limit "+start+","+pageSize;
			StringUtil.xprint("sql="+sql);
			goodList = dbManager.queryForList(sql);
			
			if(CTUtils.isNotEmpty(goodList)){
				String jg[] = null;
				String fc[] = null;
				String tag[] = null;
				
				StringBuffer jgBuffer = null;
				StringBuffer fcBuffer = null;
				StringBuffer tagBuffer = null;
				goodList1 = new ArrayList<Dto>();
				for (Dto map : goodList) {
					
					jgBuffer = new StringBuffer("");
					fcBuffer = new StringBuffer("");
					tagBuffer = new StringBuffer("");
					
					String tags = (String)map.get("tags");
					String sku_price = (String)map.get("sku_price");
					String sku_pmoney = (String)map.get("sku_pmoney");
					map.put("level_name", CTUtils.getCodeName("CULEVEL", map.getAsString("level")));
					
					if(CTUtils.isNotEmpty(tags)){
						tag = tags.split(",");
						for (String tg : tag) {
							String tagName = CTUtils.getCodeName("GOOD_TAG", tg);
							String color = CTUtils.getIndxCodeValue("GOOD_TAG", tg, "remark");
							tagBuffer.append("<span class=\"label label-"+tagName+"\" style=\"background-color:#"+color+";\">"+tagName+"</span>");
						}
						map.put("tagsStr", tagBuffer.toString());
					}
					
					//处理价格和分成
					if(CTUtils.isNotEmpty(sku_price)&& CTUtils.isNotEmpty(sku_pmoney))
						jg = sku_price.split("\\|");
					    fc = sku_pmoney.split("\\|");
					    for (int i = 0; i < jg.length; i++) {
					    	if(i%2==0){
					    	   jgBuffer.append("<p><span class=\"label\" style=\"background-color:#f49800;\">套餐 "+(i+1)+"：￥"+jg[i]+"/"+fc[i]+"</span></p>");
					    	}else{
					    	   jgBuffer.append("<p><span class=\"label\" style=\"background-color:#7dbe26;\">套餐 "+(i+1)+"：￥"+jg[i]+"/"+fc[i]+"</span></p>");
					    	}
						}
					    map.put("jgStr", jgBuffer.toString());
					    
					/*if(CTUtils.isNotEmpty(sku_price)){
						jg = sku_price.split("\\|");
						for (String jgStr : jg) {
							jgBuffer.append("<p><span class=\"text-yellow text-bold\">￥"+jgStr+"</span></p>");
						}
						map.put("jgStr", jgBuffer.toString());
					}
					
					if(CTUtils.isNotEmpty(sku_pmoney)){
						fc = sku_pmoney.split("\\|");
						for (String fcStr : fc) {
							fcBuffer.append("<p><span class=\"text-green text-bold\">￥"+fcStr+"</span></p>");
						}
						map.put("fcStr", fcBuffer.toString());
					}*/
					
					

					goodList1.add(map);
				}
			}
			
			pager = dbManager.getPageModel(goodList1, start,pageSize, totalCount);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return pager;
	}
	
	//获取所有文案
	public static List<Dto> getCCopyList(Dto paramDto) {
		List<Dto> goodCopyList = null;
		DBManager dbManager = new DBManager();
		try {
			String sql = "select * from wxb_good_copy t where 1=1 ";
			String copy_id = paramDto.getAsString("copy_id");
			String customer_id = paramDto.getAsString("customer_id");
			if(CTUtils.isNotEmpty(copy_id)){
				sql+=" and t.copy_id = '"+copy_id+"' ";
			}
			if(CTUtils.isNotEmpty(customer_id)){//添加商户过滤
				sql+=" and t.customer_id = '"+customer_id+"' ";
			}
			StringUtil.xprint("sql="+sql);
			goodCopyList = dbManager.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return goodCopyList;
	}
	
	/**
	 * 获取商品文案列表
	 * @param paramDto 参数
	 */
	public static PageModel getGoodCopyList(Dto paramDto) {
		List<Dto> goodCopyList = null;
		DBManager dbManager = new DBManager();
		try {
			String sql = "select * from wxb_good_copy t where 1=1 ";
			String customer_id = paramDto.getAsString("customer_id");
			String alisaname = paramDto.getAsString("alisaname");
			String copy_id = paramDto.getAsString("copy_id");
			int pageSize = 0;
			if(CTUtils.isEmpty(paramDto.getAsInteger("pageSize"))){
				pageSize = CTConstants.PAGER_MSIZE;
			}else{
				pageSize = paramDto.getAsInteger("pageSize");
			}
			int start = 0;
			if(CTUtils.isNotEmpty(paramDto.getAsInteger("pager.offset"))){
				start = paramDto.getAsInteger("pager.offset");
			}
			
			if(CTUtils.isNotEmpty(customer_id)){//添加商户过滤
				sql+=" and t.customer_id = '"+customer_id+"' ";
			}
			if(CTUtils.isNotEmpty(copy_id)){
				sql+=" and t.copy_id = '"+copy_id+"' ";
			}
			
			if(CTUtils.isNotEmpty(alisaname)){//添加过滤
				sql+=" and (t.copy_title like '%"+alisaname+"%' or t.copy_id like '%"+alisaname+"%') ";
			}
			
			//处理分页
			int totalCount = dbManager.getTotalCount(sql);
			sql+=" order by t.order_no asc limit "+start+","+pageSize;
			StringUtil.xprint("sql="+sql);
			goodCopyList = dbManager.queryForList(sql);
			pager = dbManager.getPageModel(goodCopyList, start,pageSize, totalCount);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return pager;
	}
	
	
	
	/**
	 * 商户充值信息列表
	 * @param paramDto 参数
	 */
	public static PageModel getCustChargeList(Dto paramDto) {
		List<Dto> chargeList = null;
		DBManager dbManager = new DBManager();
		try {
			String sql = "select * from wxb_charge_log t where 1=1 ";
			String customer_id = paramDto.getAsString("customer_id");
			int pageSize = 0;
			if(CTUtils.isEmpty(paramDto.getAsInteger("pageSize"))){
				pageSize = CTConstants.PAGER_MSIZE;
			}else{
				pageSize = paramDto.getAsInteger("pageSize");
			}
			int start = 0;
			if(CTUtils.isNotEmpty(paramDto.getAsInteger("pager.offset"))){
				start = paramDto.getAsInteger("pager.offset");
			}
			
			if(CTUtils.isNotEmpty(customer_id)){//添加商户过滤
				sql+=" and t.customer_id = '"+customer_id+"' ";
			}
			
			if(CTUtils.isNotEmpty(paramDto.getAsString("s_date"))){
			   sql+=" and t.charge_time >= '"+paramDto.getAsString("s_date")+" 00:00:00' ";
			}
			
			if(CTUtils.isNotEmpty(paramDto.getAsString("s_date"))){
			   sql+=" and t.charge_time <= '"+paramDto.getAsString("e_date")+" 23:59:59' ";
			}
			
			//处理分页
			int totalCount = dbManager.getTotalCount(sql);
			sql+=" order by t.charge_time desc limit "+start+","+pageSize;
			StringUtil.xprint("sql="+sql);
			chargeList = dbManager.queryForList(sql);
			
			pager = dbManager.getPageModel(chargeList, start,pageSize, totalCount);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return pager;
	}
	
	/**
	 * 通过ID获取订单信息
	 * @param order_id
	 * @return
	 */
	public static Dto getOrderByID(String order_id) {
		Dto orderInfoDto = null;
		DBManager dbManager = new DBManager();
		try {
			String sql = "select * from wxb_order t where t.order_id='"+order_id+"'";
			orderInfoDto = dbManager.queryForList(sql).get(0);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return orderInfoDto;
	}
	
	/**
	 * 获取订单信息列表
	 * @param paramDto
	 * @return
	 */
	public  static PageModel getOrderList(Dto paramDto) {
		List<Dto> orderList = null;
		DBManager dbManager = new DBManager();
		try {
			String sql = "select t.*,t2.good_name,t3.sku_name,t3.sku_price,t3.sku_pmoney,t4.customer_name,t4.wxh,t4.QQ,t5.name as user_name,t5.email,t5.account,t5.qq_num,t5.phone,(select t1.channel_name from wxb_channel t1 where t.channel_id=t1.channel_id ) as channel_name " +
					"from wxb_order t,wxb_good t2 ,wxb_good_sku t3,wxb_customer t4,wxb_memeber t5  where t.good_id=t2.good_id and t.sku_id = t3.sku_id  and t2.customer_id = t4.customer_id and t.user_id = t5.memeber_id ";
			int pageSize = 0;
			if(CTUtils.isEmpty(paramDto.getAsInteger("pageSize"))){
				pageSize = CTConstants.PAGER_MSIZE;
			}else{
				pageSize = paramDto.getAsInteger("pageSize");
			}
			int start = 0;
			if(CTUtils.isNotEmpty(paramDto.getAsInteger("pager.offset"))){
				start = paramDto.getAsInteger("pager.offset");
			}
			
			if(CTUtils.isNotEmpty(paramDto.getAsString("status"))){//添加状态
				sql+=" and t.state = '"+paramDto.getAsString("status")+"' ";
			}
			
			if(CTUtils.isNotEmpty(paramDto.getAsString("user_id"))){
				sql+=" and t.user_id = '"+paramDto.getAsString("user_id")+"' ";
			}
			
			if(CTUtils.isNotEmpty(paramDto.getAsString("recom_user"))){
				sql+=" and t5.recom_user = '"+paramDto.getAsString("recom_user")+"' ";
			}
			
			if(CTUtils.isNotEmpty(paramDto.getAsString("order_id"))){//单取订单
				sql+=" and t.order_id = '"+paramDto.getAsString("order_id")+"' ";
			}
			
			if(CTUtils.isNotEmpty(paramDto.getAsString("goods_id"))){//商品过滤
				sql+=" and t.good_id = '"+paramDto.getAsString("goods_id")+"' ";
			}
			
			if(CTUtils.isNotEmpty(paramDto.getAsString("channel_id"))){//商品过滤
				sql+=" and t.channel_id = '"+paramDto.getAsString("channel_id")+"' ";
			}
			
			if(CTUtils.isNotEmpty(paramDto.getAsString("courier_id"))){//商品过滤
				sql+=" and t.courier_id = '"+paramDto.getAsString("courier_id")+"' ";
			}
			
			if(paramDto.getAsString("d_type").equals("1")){//下单时间处理
				
				if(CTUtils.isNotEmpty(paramDto.getAsString("s_date"))){
				   sql+=" and t.order_time >= '"+paramDto.getAsString("s_date")+" 00:00:00' ";
				}
				
				if(CTUtils.isNotEmpty(paramDto.getAsString("s_date"))){
				   sql+=" and t.order_time <= '"+paramDto.getAsString("e_date")+" 23:59:59' ";
				}
				
			}
			
			//处理分页
			int totalCount = dbManager.getTotalCount(sql);
			sql+=" order by t.order_time desc  limit "+start + "," + pageSize;
			StringUtil.xprint("sql="+sql);
			orderList = dbManager.queryForList(sql);
			if(!StringUtil.checkListBlank(orderList)){
				for (Dto map : orderList) {
					
					String buyer_name = map.getAsString("buyer_name");
					String buyer_phone = map.getAsString("buyer_phone");
					String sku_name = map.getAsString("sku_name");
					String good_name = map.getAsString("good_name");
					String state = map.getAsString("state");
					String sender_type = map.getAsString("sender_type");
					
					String city = map.getAsString("city");
					String area = map.getAsString("area");
					String address = map.getAsString("address");
					String province = map.getAsString("province");
					
					//号码归属
					if(CTUtils.isNotEmpty(buyer_phone)){
					   //String mlocation = MobileLocationUtil.getMobileLocation(buyer_phone);
					   //map.put("mlocation", mlocation);
					}
					
					if(CTUtils.isNotEmpty(address)){
						if(state.equals("3")||state.equals("6")){
						   map.put("userAddr",map.getAsString("buyer_name")+"，"+map.getAsString("buyer_phone")+"，"+province+city+area+" "+address);
						}else{
							if(CTUtils.isNotEmpty(buyer_name)){
								map.put("buyer_name",buyer_name.substring(0, 1)+"**");
							}
							if(CTUtils.isNotEmpty(buyer_phone)){
								map.put("buyer_phone",buyer_phone.substring(0, 3)+"*****"+buyer_phone.substring(8, 11));
							}
						    map.put("userAddr",map.getAsString("buyer_name")+"，"+map.getAsString("buyer_phone")+"，"+province+city+area+"******");
						}
					}
					
					map.put("state_name", CTUtils.getCodeName("ORDER_STATE", state));
					map.put("sender_name", CTUtils.getCodeName("SENDER_TYPE", sender_type));
					//处理商品信息
					if(CTUtils.isNotEmpty(sku_name)){
						int buy_num = map.getAsInteger("buy_num");
						double sku_price =Double.valueOf(map.getAsString("sku_price"));
						double totalMoney = sku_price*buy_num;
						String good_str =  good_name+"，"+sku_name+"*"+buy_num+"，"+totalMoney;
						map.put("good_str", good_str);
					}
					
				}
			
		    }
			pager = dbManager.getPageModel(orderList, start,pageSize, totalCount);
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return pager;
	}
	
	
	/**
	 * 获取订单信息列表
	 * @param paramDto
	 * @return
	 */
	public  static List<Dto> getCOrderList(Dto paramDto) {
		List<Dto> orderList = null;
		DBManager dbManager = new DBManager();
		try {
			String sql = "select t.*,t2.good_name,t3.sku_name,t3.sku_price,t3.sku_pmoney,t4.customer_name,t4.wxh,t4.QQ,t5.name as user_name,t5.email,t5.account,t5.qq_num,t5.phone,t6.visit_ip,(select t1.channel_name from wxb_channel t1 where t.channel_id=t1.channel_id ) as channel_name " +
					"from wxb_order t,wxb_good t2 ,wxb_good_sku t3,wxb_customer t4,wxb_memeber t5,wxb_visit_log t6  where t.good_id=t2.good_id and t.sku_id = t3.sku_id  and t2.customer_id = t4.customer_id and t.user_id = t5.memeber_id and t. order_id = t6. order_id ";
			int pageSize = 0;
			if(CTUtils.isEmpty(paramDto.getAsInteger("pageSize"))){
				pageSize = CTConstants.PAGER_MSIZE;
			}else{
				pageSize = paramDto.getAsInteger("pageSize");
			}
			int start = 0;
			if(CTUtils.isNotEmpty(paramDto.getAsInteger("pager.offset"))){
				start = paramDto.getAsInteger("pager.offset");
			}
			
			if(CTUtils.isNotEmpty(paramDto.getAsString("status"))){//添加状态
				sql+=" and t.state = '"+paramDto.getAsString("status")+"' ";
			}
			
			if(CTUtils.isNotEmpty(paramDto.getAsString("user_id"))){
				sql+=" and t.user_id = '"+paramDto.getAsString("user_id")+"' ";
			}
			
			if(CTUtils.isNotEmpty(paramDto.getAsString("order_id"))){//单取订单
				sql+=" and t.order_id = '"+paramDto.getAsString("order_id")+"' ";
			}
			
			if(CTUtils.isNotEmpty(paramDto.getAsString("goods_id"))){//商品过滤
				sql+=" and t.good_id = '"+paramDto.getAsString("goods_id")+"' ";
			}
			
			if(CTUtils.isNotEmpty(paramDto.getAsString("channel_id"))){//商品过滤
				sql+=" and t.channel_id = '"+paramDto.getAsString("channel_id")+"' ";
			}
			
			if(CTUtils.isNotEmpty(paramDto.getAsString("courier_id"))){//商品过滤
				sql+=" and t.courier_id = '"+paramDto.getAsString("courier_id")+"' ";
			}
			
			if(paramDto.getAsString("d_type").equals("1")){//下单时间处理
				
				if(CTUtils.isNotEmpty(paramDto.getAsString("s_date"))){
				   sql+=" and t.order_time >= '"+paramDto.getAsString("s_date")+" 00:00:00' ";
				}
				
				if(CTUtils.isNotEmpty(paramDto.getAsString("s_date"))){
				   sql+=" and t.order_time <= '"+paramDto.getAsString("e_date")+" 23:59:59' ";
				}
				
			}
			
			//处理分页
			sql+=" order by t.order_time desc  limit "+start+","+pageSize;
			StringUtil.xprint("sql="+sql);
			orderList = dbManager.queryForList(sql);
			if(!StringUtil.checkListBlank(orderList)){
				for (Dto map : orderList) {
					
					String buyer_name = map.getAsString("buyer_name");
					String buyer_phone = map.getAsString("buyer_phone");
					String sku_name = map.getAsString("sku_name");
					String good_name = map.getAsString("good_name");
					String state = map.getAsString("state");
					String order_type = map.getAsString("order_type");
					
					String city = map.getAsString("city");
					String area = map.getAsString("area");
					String address = map.getAsString("address");
					String province = map.getAsString("province");
					
					if(CTUtils.isNotEmpty(buyer_name)){
						map.put("buyer_name",buyer_name);
					}
					if(CTUtils.isNotEmpty(buyer_phone)){
						map.put("buyer_phone",buyer_phone);
					}
					
					if(CTUtils.isNotEmpty(address)){
						map.put("userAddr",map.getAsString("buyer_name")+"，"+map.getAsString("buyer_phone")+"，"+province+city+area);
					}
					
					map.put("state_name", CTUtils.getCodeName("ORDER_STATE", state));
					map.put("type_name", CTUtils.getCodeName("ORDER_TYPE", order_type));
					
					//处理商品信息
					if(CTUtils.isNotEmpty(sku_name)){
						int buy_num = map.getAsInteger("buy_num");
						double sku_price =Double.valueOf(map.getAsString("sku_price"));
						double totalMoney = sku_price*buy_num;
						String good_str =  good_name+"，"+sku_name+"*"+buy_num+"，"+totalMoney;
						map.put("good_str", good_str);
					}
						
				}
			
		    }
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return orderList;
	}
	
	/**
	 * 获取首页日期统计表
	 * @param paramDto
	 * @return
	 */
	public static Dto getHomeDateCount(Dto paraDto) {
		Dto dataDto = null;
		DBManager dbManager = new DBManager();
		try {
			String sqlString =  " select t.order_id,t1.sku_price*t.buy_num as zjg,t.user_id,t.good_id,t.state, "+
						        " case t.state when 1 then 1 else 0 end as xd,  "+
						        " case t.state when 1 then  t1.sku_pmoney*t.buy_num else 0 end as xdfc, "+
						        " case t.state when 2 then 1 else 0 end as df , "+
						        " case t.state when 2 then  t1.sku_pmoney*t.buy_num else 0 end as dffc, "+
						        " case t.state when 3 then 1 else 0 end as qx,  "+
						        " case t.state when 3 then  t1.sku_pmoney*t.buy_num else 0 end as qxfc, "+
						        " case t.state when 4 then 1 else 0 end as dq,  "+
						        " case t.state when 4 then  t1.sku_pmoney*t.buy_num else 0 end as dqfc, "+
						        " case t.state when 5 then 1 else 0 end as yf,  "+
						        " case t.state when 5 then  t1.sku_pmoney*t.buy_num else 0 end as yffc, "+
						        " case t.state when 6 then 1 else 0 end as js,  "+
						        " case t.state when 6 then  t1.sku_pmoney*t.buy_num else 0 end as jsfc, "+
						        " case t.state when 7 then 1 else 0 end as qs,  "+
						        " case t.state when 7 then  t1.sku_pmoney*t.buy_num else 0 end as qsfc, "+
						        " (select o.order_id from wxb_order_count o  "+
						        " where o.order_id = t.order_id and o.state_id=3) as yjsd, "+
						        " (select p.order_pmoney from wxb_order_count p  "+
						        " where p.order_id = t.order_id and p.state_id=3) as yjsdfc, "+
								" case t.state when 8 then 1 else 0 end as th , "+
								" case t.state when 8 then  t1.sku_pmoney*t.buy_num else 0 end as thfc "+
								" from wxb_order t,wxb_good_sku t1 where t.sku_id = t1.sku_id ";
			
							    if(CTUtils.isNotEmpty(paraDto.getAsString("user_id"))){
						           sqlString += " and t.user_id = '"+paraDto.getAsString("user_id")+"'";
						        }
					        
						        if(CTUtils.isNotEmpty(paraDto.getAsString("start_date"))){
						     	   sqlString += " and t.order_time >= '"+paraDto.getAsString("start_date")+" 00:00:00'";
						        }
						
						        if(CTUtils.isNotEmpty(paraDto.getAsString("end_date"))){
						     	   sqlString += " and t.order_time <= '"+paraDto.getAsString("end_date")+" 23:59:59'";
						        }
				   
								StringUtil.xprint("querySQL="+sqlString);
								//查询合计数据
								String countSql =  " select "+
								" count(tm.order_id) as zds, "+
								" sum(tm.zjg) as zje, "+
								" sum(tm.xd) as xds, "+
								" sum(tm.xdfc) as xdfc, "+
								" sum(tm.df) as dfs, "+
								" sum(tm.dffc) as dffc, "+
								" sum(tm.qx) as qxs, "+
								" sum(tm.qxfc) as qxfc, "+
								" sum(tm.dq) as dqs, "+
								" sum(tm.dqfc) as dqfc, "+
								" sum(tm.yf) as yfs, "+
								" sum(tm.yffc) as yffc, "+
								" sum(tm.js) as jss, "+
								" sum(tm.jsfc) as jsfc, "+
								" sum(tm.qs) as qss, "+
								" sum(tm.qsfc) as qsfc, "+
								" count(tm.yjsd) as yjsds, "+
								" sum(tm.yjsdfc) as yjsdfc, "+
								" sum(tm.th) as ths, "+
								" sum(tm.thfc) as thfc "+
								"from ( "+sqlString+")tm where 1=1 ";
								StringUtil.xprint("countSQL="+countSql);
								List<Dto> dataList = dbManager.queryForList(countSql);
								if(!StringUtil.checkListBlank(dataList)){
									dataDto = dataList.get(0);
								}else{
									dataDto = new BaseDto();
								}

			} catch (Exception e) {
				e.printStackTrace();
			}
		return dataDto;
	}
	
	/**
	 * 获取当日访问量
	 * @return
	 */
	public static Dto getVisitCount(Dto paraDto){
		Dto dataDto = null;
		DBManager dbManager = new DBManager();
		try {
			
			String sqlString = " select sum(tm.dds) as order_num,"+
				" count(tm.visit_ip) as ip_num,"+
				" sum(tm.lls) as vit_num "+
				" from ( "+
				"  select tt.visit_ip, "+
				"  count(tt.order_id) as dds, "+
				"  count(tt.visit_ip) as ips, "+
				"  count(tt.visit_num) as lls "+
				"  from ( "+
				"  select t.log_id, "+
				"  t.good_id, "+
				"  t.user_id, "+
				"  t.channel_id, "+
				"  t.visit_ip, "+
				"  t.visit_num, "+
				"  t.visit_time, "+
				"  t.order_id "+
				"from wxb_visit_log t where 1 = 1 ";
			
			    if(CTUtils.isNotEmpty(paraDto.getAsString("user_id"))){
		           sqlString += " and t.user_id = '"+paraDto.getAsString("user_id")+"'";
		        }
				if(CTUtils.isNotEmpty(paraDto.getAsString("start_date"))){
		     	   sqlString += " and t.visit_time >= '"+paraDto.getAsString("start_date")+" 00:00:00'";
		        }
		
		        if(CTUtils.isNotEmpty(paraDto.getAsString("end_date"))){
		     	   sqlString += " and t.visit_time <= '"+paraDto.getAsString("end_date")+" 23:59:59'";
		        }
		        
			    sqlString +="  ) tt group by tt.visit_ip "+
				" ) tm where 1=1";

			StringUtil.xprint("sqlString="+sqlString);
			List<Dto> dataList = dbManager.queryForList(sqlString);
			if(!StringUtil.checkListBlank(dataList)){
				dataDto = dataList.get(0);
			}else{
				dataDto = new BaseDto();
			}
        } catch (Exception e) {
		  e.printStackTrace();
		}
		
		return dataDto;
	}
	
	/**
	 * 获取订单统计数据
	 * @param paramDto
	 * @return
	 */
	public static Dto getOrderCountData(Dto paramDto) {
		Dto dataDto = null;
		DBManager dbManager = new DBManager();
		try {
			String sqlString="  select "+
			"  sum(tt.xd) as xds, "+
			"  sum(tt.xdfc) as xd_fc, "+
			"  sum(tt.df) as dfs, "+
			"  sum(tt.dffc) as df_fc, "+
			"  sum(tt.qx) as qxs, "+
			"  sum(tt.qxfc) as qx_fc, "+
			"  sum(tt.dq) as dqs, "+
			"  sum(tt.dqfc) as dq_fc, "+
			"  sum(tt.yf) as yfs, "+
			"  sum(tt.yffc) as yf_fc, "+
			"  sum(tt.js) as jss, "+
			"  sum(tt.jsfc) as js_fc, "+
			"  sum(tt.qs) as qss, "+
			"  sum(tt.qsfc) as qs_fc, "+
			"  sum(tt.th) as ths, "+
			"  sum(tt.thfc) as th_fc "+
			"  from ( "+
			"  select  "+
			"  tm.xd, "+
			"  case tm.state when 1 then (tm.xd * tm.sku_pmoney) else 0 end as xdfc, "+
			"  tm.df, "+
			"  case tm.state when 2 then (tm.df * tm.sku_pmoney) else 0 end as dffc, "+ 
			"  tm.qx, "+
			"  case tm.state when 3 then (tm.qx * tm.sku_pmoney) else 0 end as qxfc, "+
			"  tm.dq, "+
			"  case tm.state when 4 then (tm.dq * tm.sku_pmoney) else 0 end as dqfc, "+
			"  tm.yf, "+
			"  case tm.state when 5 then (tm.yf * tm.sku_pmoney) else 0 end as yffc, "+
			"  tm.js, "+
			"  case tm.state when 6 then (tm.js * tm.sku_pmoney) else 0 end as jsfc, "+ 
			"  tm.qs, "+
			"  case tm.state when 7 then (tm.qs * tm.sku_pmoney) else 0 end as qsfc, "+
			"  tm.th, "+
			"  case tm.state when 8 then (tm.th * tm.sku_pmoney) else 0 end as thfc  "+
			"  from ( "+
			"  select t.order_id,t.user_id,t.good_id,t.state ,t1.sku_pmoney, "+
			"  case t.state when 1 then 1 else 0 end as xd, "+
			"  case t.state when 2 then 1 else 0 end as df , "+
			"  case t.state when 3 then 1 else 0 end as qx,  "+
			"  case t.state when 4 then 1 else 0 end as dq, "+
			"  case t.state when 5 then 1 else 0 end as yf, "+
			"  case t.state when 6 then 1 else 0 end as js, "+
			"  case t.state when 7 then 1 else 0 end as qs, "+
			"  case t.state when 8 then 1 else 0 end as th "+
			"  from wxb_order t,wxb_good_sku t1 where t.sku_id = t1.sku_id "+
			"  ) tm where 1=1";
			
			if(CTUtils.isNotEmpty(paramDto.getAsString("user_id"))){
		           sqlString += " and tm.user_id = '"+paramDto.getAsString("user_id")+"' ";
		    }
			
			sqlString += "  )tt where 1=1";
			
			//StringUtil.xprint("csqlString="+sqlString);
			List<Dto> dataList = dbManager.queryForList(sqlString);
			if(!StringUtil.checkListBlank(dataList)){
				dataDto = dataList.get(0);
			}else{
				dataDto = new BaseDto();
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dataDto;
	}
	
	/**
	 * 统计邀请奖励总额
	 * @param paramDto
	 * @return
	 */
	public static Dto getVisitRewardCount(Dto paramDto) {
		Dto dataDto = null;
		DBManager dbManager = new DBManager();
		try {
			String sqlString=" select tn.jls, tn.yjfc,tn.sjfc "+
				"  from (select count(tm.order_id) as jls, "+
				"  sum(tm.gms * tm.fc) * 0.05 as yjfc, "+
				"  sum(tm.gms * tm.jsfc) * 0.05 as sjfc "+
				"  from (select t.order_id, "+
				"  t. buy_num as gms, "+
				"  t2. sku_pmoney as fc, "+
				"  (select p.order_pmoney from wxb_order_count p where p.order_id = t.order_id and p.state_id=3) as jsfc "+
				"  from wxb_order t, wxb_good_sku t2 "+
				"  where t. sku_id = t2.sku_id "+
				"  and t.user_id in "+
				"  (select t1.memeber_id "+
				"  from wxb_memeber t1 "+
				"  where t1. recom_user = '"+paramDto.getAsString("visit_code")+"')) tm "+
				"  where 1 = 1) tn "+
				"  where 1 = 1";
			StringUtil.xprint("vsqlString="+sqlString);
			List<Dto> dataList = dbManager.queryForList(sqlString);
			if(!StringUtil.checkListBlank(dataList)){
				dataDto = dataList.get(0);
			}else{
				dataDto = new BaseDto();
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dataDto;
	}
	
	
	/**
	 * 获取用户结算返款合计
	 * @param param
	 * @return
	 */
	public static Dto getUserMoneyCount(Dto param) {
		Dto dataDto = null;
		DBManager dbManager = new DBManager();
		try {
			
			String sqlString = " select tt.user_id,"+
				" sum(tt.order_money) as ddsy,"+
				" sum(tt.visit_money) as yqsy,"+
				" sum(tt.own_money) as zfk,"+
				" sum(tt.wzf) as zwzf,"+
				" sum(tt.yzf) as zyzf"+
				" from (select t. user_id,"+
				" t. order_money,"+
				" t. visit_money,"+
				" t. own_money,"+
				" (select t2. own_money a "+
				" from wxb_user_money t2 "+
				" where t2. um_id = t.um_id "+
				" and t. state = 0) as wzf,"+
				" (select t1. own_money a "+
				" from wxb_user_money t1 "+
				" where t1. um_id = t.um_id "+
				" and t. state = 1) as yzf "+
				" from wxb_user_money t "+
				" where  t.user_id = '"+param.getAsString("user_id")+"') tt"+
				" where 1 = 1"+
				" group by tt.user_id";
			List<Dto> dataList = dbManager.queryForList(sqlString);
			if(!StringUtil.checkListBlank(dataList)){
				dataDto = dbManager.queryForList(sqlString).get(0);
			}else{
				dataDto = new BaseDto();
			}
			//StringUtil.xprint("rcountSQL="+sqlString);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dataDto;
	}
	
	
	/**
	 * 保存订单信息
	 * @param paramDto
	 * @return
	 */
	public static boolean saveOrderInfo(Dto paramDto) {
		boolean temp = false;
		String sql = null;
		DBManager dbManager = new DBManager();
		try {
			
	          sql = "insert into wxb_order(" +
					"order_id," +
					"buyer_phone," +
					"buyer_name," +
					"good_id," +
					"order_time," +
					"channel_id," +
					"state," +
					"order_type," +
					"buy_num," +
					"province," +
					"city," +
					"area," +
					"address," +
					"buyer_reamrk,"+
					"pay_type,"+
					"sku_id,"+
					"user_id,order_ip"+
                  ")values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	          
	          String params[] = new String[18];
	          params[0] = paramDto.getAsString("order_id");
	          params[1] = paramDto.getAsString("buyer_phone");
	          params[2] = paramDto.getAsString("buyer_name");
	          params[3] = paramDto.getAsString("good_id");
	          params[4] = paramDto.getAsString("order_time");
	          params[5] = paramDto.getAsString("channel_id");
	          params[6] = paramDto.getAsString("state");
	          params[7] = paramDto.getAsString("order_type");
	          params[8] = paramDto.getAsString("buy_num");
	          params[9] = paramDto.getAsString("province");
	          params[10] = paramDto.getAsString("city");
	          params[11] = paramDto.getAsString("area");
	          params[12] = paramDto.getAsString("address");
	          params[13] = paramDto.getAsString("buyer_reamrk");
	          params[14] = paramDto.getAsString("pay_type");
	          params[15] = paramDto.getAsString("sku_id");
	          params[16] = paramDto.getAsString("user_id");
	          params[17] = paramDto.getAsString("order_ip");
	          
			  int rt = dbManager.updateBySQL(sql, params);   
			  if(rt>0){
			     temp = true;
			  }
			  StringUtil.xprint("保存订单信息成功....");
		} catch (Exception e) {
			StringUtil.xprint("保存订单信息失败....");
			e.printStackTrace();
		}finally{
			//处理记录日志
			Dto logDto =new BaseDto();
			logDto.put("otype", "INSERT");
			logDto.put("msql_text", sql);
			logDto.put("createtime", CTUtils.getCurrentTime());
			logDto.put("state", temp==true?"0":"1");
			addMsSqlLog(logDto); 
		}
		return temp;
	}

	public PageModel getPager() {
		return pager;
	}

	public void setPager(PageModel pager) {
		this.pager = pager;
	}
	
	/**
	 * 保存sql日志信息
	 * @param msDto
	 * @return
	 */
	public static void addMsSqlLog(Dto msDto) {
		DBManager dbManager = new DBManager();
		try {
			String sql = "insert into client_msql_log(otype,msql_text,createtime,state)values(?,?,?,?)";
			String [] vals = new String[msDto.size()];
			vals[0] = msDto.getAsString("otype");
			vals[1] = msDto.getAsString("msql_text");
			vals[2] = msDto.getAsString("createtime");
			vals[3] = msDto.getAsString("state");
			
			//处理文件保存
			saveLogToFile(sql, vals);
			
            //保存数据
			int rstt = dbManager.updateBySQL(sql, vals);
			if(rstt>0){
				StringUtil.xprint("保存sql日志信息成功!");
			}else{
				StringUtil.xprint("保存sql日志信息失败!");
			}

		} catch (Exception e) {
			StringUtil.xprint("保存sql日志信息出错了!");
			e.printStackTrace();
		}
	}
	
	/**
	 * 保存数据到文件内
	 * @param msDto
	 */
	public static void saveLogToFile(String sql,String[] vals) {
		try {
			
			String fileName = "";
			String classPath = JdbcTemplateTool.class.getResource("/").getPath();
		    String savePath = CTUtils.getWebRootPaht(classPath, 1)+"/mslgs/";
		    //处理一级文件夹
		    File saveDirFile = new File(savePath);
			if (!saveDirFile.exists()) {
				saveDirFile.mkdirs();
			}
			//处理二级文件夹 使用日期分类
			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
			String ymd = sdf.format(new Date());
			savePath += ymd + "/";
			File dirFile = new File(savePath);
			if (!dirFile.exists()) {
				dirFile.mkdirs();
			}
			
			//处理文件名称
			String saveText = null;
			String otype = vals[0];
			if(otype.endsWith("INSERT")){
				fileName = ymd + "_insert.txt";
				saveText = "【"+vals[2]+"】执行了插入语句为：【"+vals[1]+"】 状态为【"+vals[3]+"】";
			}
			if(otype.endsWith("UPDATE")){
				fileName = ymd + "_update.txt";
				saveText = "【"+vals[2]+"】执行了更新语句为：【"+vals[1]+"】 状态为【"+vals[3]+"】";
			}
			if(otype.endsWith("DELETE")){
				fileName = ymd + "_delete.txt";
				saveText = "【"+vals[2]+"】执行了删除语句为：【"+vals[1]+"】 状态为【"+vals[3]+"】";
			}
			
			//处理写入字符串
			savePath += fileName;
			StringUtil.xprint("保存文件路径为："+savePath);
			FileUtils.appendMethodB(savePath, saveText);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获取渠道集合
	 */
	public static List<Channel> QueryList(String uid) {
		List<Channel> goodList = null;
		DBManager dbManager = new DBManager();
		try {
			String sql = "select channel_id ,channel_name ,channel_txt from wxb_channel "
				+ " where  channel_uid='" + uid + "'  order by channel_id";
		    goodList = dbManager.queryList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return goodList;
	}
	
	/**
	 * 获取邀请列表
	 */
	public static List<Dto> queryRewardList(String uid) {
		List<Dto> goodList = null;
		DBManager dbManager = new DBManager();
		try {
			String sql = "select o.order_id ,"
					+ "m.name,"
					+ "m.account ,"
					+ "m.qq_num ,"
					+ "g.good_name ,"
					+ "o.state,"
					+ "o.buy_num ,"
					+ "o.order_remark,"
					+ "s.sku_pmoney,"
					+ "o.buy_num * s.sku_pmoney as fc "
					+ "from wxb_memeber m ,wxb_order o ,wxb_good g ,wxb_good_sku s "
					+ " where 1=1 "
					+ " and m.recom_user='"+uid+"' "
					+ " and m.memeber_id=o.user_id "
					+ " and o.good_id=g.good_id  "
					+ " and o.sku_id=s.sku_id";
			StringUtil.xprint(sql); 
			goodList = dbManager.queryForList(sql);
			if(!StringUtil.checkListBlank(goodList)){
				for (Dto reward : goodList) {
					reward.put("state_name",CTUtils.getCodeName("ORDER_STATE", reward.getAsString("state")));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return goodList;
	}
	
	/**
	 * 获取邀请人列表
	 * @param visitCode
	 * @return
	 */
	public static List<Dto> queryRPeopleList(String visitCode) {
		List<Dto> pList = null;
		DBManager dbManager = new DBManager();
		try {
			String sql = "select t.* from wxb_memeber t where t.recom_user ='"+visitCode+"' ";
			StringUtil.xprint(sql); 
			pList = dbManager.queryForList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return pList;
	}

	/**
	 * 获取渠道集合
	 */
	public static List<Channel> QueryList(String uid, String id) {
		List<Channel> goodList = null;
		DBManager dbManager = new DBManager();
		try {
			String sql = "select channel_id ,channel_name ,channel_txt from wxb_channel "
				+ " where  channel_uid='" + uid + "' and channel_id='" + id +"'";
		    goodList = dbManager.queryList(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return goodList;

	}

	/**
	 * 修改渠道
	 */
	public static boolean UpdateDate(String uid, String id, String name,String txt) {
		boolean temp = false;
		DBManager dbManager = new DBManager();
		try {
			String sql = "update wxb_channel set " + "channel_name='" + name + "',"
			+ "channel_txt='" + txt + "'  " + "where  " + "channel_uid='"
			+ uid + "' and channel_id='" + id +"'";
			temp = dbManager.insertBySQL(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return temp;
	}

	/**
	 * 增加渠道
	 */
	public static boolean AddDate(String uid, String name, String txt) {
		boolean temp = false;
		DBManager dbManager = new DBManager();
		try {
			String sql = "insert into wxb_channel "
				+ "(channel_name,channel_txt,channel_uid) " + "values ( '"
				+ name + "', '" + txt + "','" + uid + "');";
			temp = dbManager.insertBySQL(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return temp;
	}
	
	/**
	 * 增加渠道
	 */
	public static int addChannel(String uid, String name, String txt) {
		int a=0;
		DBManager dbManager = new DBManager();
		try {
			String sql = "insert into wxb_channel "
				+ "(channel_name,channel_txt,channel_uid) " + "values ( '"
				+ name + "', '" + txt + "','" + uid + "');";
			a = dbManager.addDataBySQL(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return a;
	}

	/**
	 * 删除渠道
	 */
	public static boolean deleteDate(String id) {
		boolean temp = false;
		DBManager dbManager = new DBManager();
		try {
			String sql = "delete from wxb_channel  where channel_id='" + id+"'";
			temp = dbManager.insertBySQL(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return temp;
	}
	
	/**
	 * 删除订单
	 */
	public static boolean deleteOrder(String id) {
		
		String sql = null;
		boolean temp = false;
		DBManager dbManager = new DBManager();
		try {
		    sql = "delete from wxb_order where order_id in('"+id+"')";
			temp = dbManager.insertBySQL(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			//处理记录日志
			Dto logDto =new BaseDto();
			logDto.put("otype", "DELETE");
			logDto.put("msql_text", sql);
			logDto.put("createtime", CTUtils.getCurrentTime());
			logDto.put("state", temp==true?"0":"1");
			addMsSqlLog(logDto); 
		}
		
		return temp;
	}
	
	/**
	 * 修改订单
	 */
	public static boolean updateOrder(Dto paramDto) {
		boolean temp = false;
		String sql = null;
		DBManager dbManager = new DBManager();
		try {
			sql = " update wxb_order t set ";
			if(CTUtils.isNotEmpty(paramDto.getAsString("state"))){
				sql+=" t.state='"+paramDto.getAsString("state")+"',";
			}
			if(CTUtils.isNotEmpty(paramDto.getAsString("sender_type"))){
				sql+=" t.sender_type='"+paramDto.getAsString("sender_type")+"',";
			}
			if(CTUtils.isNotEmpty(paramDto.getAsString("courier_id"))){
				sql+=" t.courier_id='"+paramDto.getAsString("courier_id")+"',";
			}
			if(CTUtils.isNotEmpty(paramDto.getAsString("order_remark"))){
				sql+=" t.order_remark='"+paramDto.getAsString("order_remark")+"',";
			}
			if(CTUtils.isNotEmpty(paramDto.getAsString("sku_id"))){
				sql+=" t.sku_id='"+paramDto.getAsString("sku_id")+"',";
			}
			if(CTUtils.isNotEmpty(paramDto.getAsString("area"))){
				sql+=" t.area='"+paramDto.getAsString("area")+"',";
			}
			if(CTUtils.isNotEmpty(paramDto.getAsString("province"))){
				sql+=" t.province='"+paramDto.getAsString("province")+"',";
			}
			if(CTUtils.isNotEmpty(paramDto.getAsString("city"))){
				sql+=" t.city='"+paramDto.getAsString("city")+"',";
			}
			if(CTUtils.isNotEmpty(paramDto.getAsString("address"))){
				sql+=" t.address='"+paramDto.getAsString("address")+"',";
			}
			if(CTUtils.isNotEmpty(paramDto.getAsString("buyer_name"))){
				sql+=" t.buyer_name='"+paramDto.getAsString("buyer_name")+"',";
			}
			if(CTUtils.isNotEmpty(paramDto.getAsString("buyer_phone"))){
				sql+=" t.buyer_phone='"+paramDto.getAsString("buyer_phone")+"',";
			}
			if(CTUtils.isNotEmpty(paramDto.getAsString("buy_num"))){
				sql+=" t.buy_num='"+paramDto.getAsString("buy_num")+"'";
			}
			
			sql+=" where t.order_id='"+paramDto.getAsString("order_id")+"'";
			temp = dbManager.insertBySQL(sql);
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			//处理记录日志
			Dto logDto =new BaseDto();
			logDto.put("otype", "UPDATE");
			logDto.put("msql_text", sql);
			logDto.put("createtime", CTUtils.getCurrentTime());
			logDto.put("state", temp==true?"0":"1");
			addMsSqlLog(logDto); 
		}
		
		return temp;
	}
	
	/**
	 * 增加日志
	 * @param order_id
	 */
	public static boolean addOrderLog(String order_id,String log_txt) {
		boolean temp = false;
		DBManager dbManager = new DBManager();
		try {
			if(CTUtils.isNotEmpty(order_id)&&CTUtils.isNotEmpty(log_txt)){
				String sql = "insert into wxb_order_log (order_id,log_txt,createtime)values(";
				       sql+="'"+order_id+"',";
				       sql+="'"+log_txt+"',";
				       sql+="'"+CTUtils.getCurrentTime()+"')";
				temp= dbManager.insertBySQL(sql);
				StringUtil.xprint("记录订单["+order_id+"]日志信息["+log_txt+"]成功！");
			}
		} catch (Exception e) {
			StringUtil.xprint("记录订单日志失败！");
			e.printStackTrace();
		}
		
		return temp;
	}
	
	/**
	 * 增加支付日志
	 * @param paramDto
	 */
	public static boolean addOrderPayLog(Dto paramDto) {
		boolean temp = false;
		String sql = null;
		DBManager dbManager = new DBManager();
		try {
			if(CTUtils.isNotEmpty(paramDto)){
				       sql = "insert into wxb_pay_log(pay_account,account_name,alipay_code,pay_time,pay_money,operater,remark,createtime,um_id,customer_id)values(";
				       sql+=" '"+paramDto.getAsString("pay_account")+"',";
				       sql+=" '"+paramDto.getAsString("account_name")+"',";
				       sql+=" '"+paramDto.getAsString("alipay_code")+"',";
				       sql+=" '"+paramDto.getAsString("pay_time")+"',";
				       sql+=" '"+paramDto.getAsString("pay_money")+"',";
				       sql+=" '"+paramDto.getAsString("operater")+"',";
				       sql+=" '"+paramDto.getAsString("remark")+"',";
				       sql+=" '"+CTUtils.getCurrentTime()+"',";
				       sql+=" '"+paramDto.getAsString("um_id")+"',";
				       sql+=" '"+paramDto.getAsString("customer_id")+"')";
				temp= dbManager.insertBySQL(sql);
				StringUtil.xprint("记录结算支付日志信息成功！");
			}
		} catch (Exception e) {
			StringUtil.xprint("记录结算支付日志信息失败！");
			e.printStackTrace();
		}finally{
			//处理记录日志
			Dto logDto =new BaseDto();
			logDto.put("otype", "INSERT");
			logDto.put("msql_text", sql);
			logDto.put("createtime", CTUtils.getCurrentTime());
			logDto.put("state", temp==true?"0":"1");
			addMsSqlLog(logDto); 
		}
		
		return temp;
	}
	
	/**
	 * 增加商品文案
	 * @param paramDto
	 */
	public static boolean addGoodCopy(Dto paramDto) {
		boolean temp = false;
		DBManager dbManager = new DBManager();
		try {
			if(CTUtils.isNotEmpty(paramDto)){
				String sql = "insert into wxb_good_copy(copy_id,copy_title,copy_link,copy_content,order_no,customer_id,type_id)values(?,?,?,?,?,?,?)";
				 
				 String values [] = new String[7];
				 String copy_id = UniqueID.getUniqueID(8, 0);
				 values[0] = copy_id;
				 values[1] = paramDto.getAsString("copy_title");
				 values[2] = paramDto.getAsString("copy_link");
				 values[3] = paramDto.getAsString("copy_content");
				 values[4] = paramDto.getAsString("order_no")==null ? "99" :paramDto.getAsString("order_no");
				 values[5] = paramDto.getAsString("customer_id");
				 values[6] = paramDto.getAsString("ctype");
				int rest= dbManager.updateBySQL(sql,values);
				if(rest>0){
					temp = true;
				}
				StringUtil.xprint("添加文案信息成功！");
			}
		} catch (Exception e) {
			StringUtil.xprint("添加文案信息失败！");
			e.printStackTrace();
		}
		
		return temp;
	}
	
	/**
	 * 修改商品文案
	 * @param paramDto
	 */
	public static boolean editGoodCopy(Dto paramDto) {
		boolean temp = false;
		DBManager dbManager = new DBManager();
		try {
			if(CTUtils.isNotEmpty(paramDto)){
				String sql = "update wxb_good_copy t set t.copy_title=?,t.copy_link=?,t.copy_content=?,t.order_no=?,t.customer_id=?,t.type_id=? where t.copy_id=? ";
				 
				 String values [] = new String[7];
				 values[0] = paramDto.getAsString("copy_title");
				 values[1] = paramDto.getAsString("copy_link");
				 values[2] = paramDto.getAsString("copy_content");
				 values[3] = paramDto.getAsString("order_no");
				 values[4] = paramDto.getAsString("customer_id");
				 values[5] = paramDto.getAsString("ctype");
				 values[6] = paramDto.getAsString("copy_id");
				int rest= dbManager.updateBySQL(sql,values);
				if(rest>0){
					temp = true;
				}
				StringUtil.xprint("修改文案信息成功！");
			}
		} catch (Exception e) {
			StringUtil.xprint("修改文案信息失败！");
			e.printStackTrace();
		}
		
		return temp;
	}
	
	/**
	 * 删除文案信息
	 * @param copy_id
	 * @param cust_id
	 * @return
	 */
	public  static boolean deleteGoodCopy(String copy_id,String cust_id) {
		boolean temp = false;
		DBManager dbManager = new DBManager();
		try {
			if(CTUtils.isNotEmpty(copy_id)){
				String sql = "delete from wxb_good_copy  where copy_id in('"+copy_id+"') and customer_id='"+cust_id+"' ";
				temp = dbManager.execute(sql);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return temp;
	}
	
	/**
	 * 删除商户商品信息
	 * @param good_id
	 * @param cust_id
	 * @return
	 */
	public  static boolean deleteCustGood(String good_id,String cust_id) {
		boolean temp = false;
		String sql = null;
		DBManager dbManager = new DBManager();
		try {
			if(CTUtils.isNotEmpty(good_id)){
			    sql = "delete from wxb_good  where good_id in('"+good_id+"') and customer_id='"+cust_id+"' ";
				temp = dbManager.execute(sql);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			//处理记录日志
			Dto logDto =new BaseDto();
			logDto.put("otype", "DELETE");
			logDto.put("msql_text", sql);
			logDto.put("createtime", CTUtils.getCurrentTime());
			logDto.put("state", temp==true?"0":"1");
			addMsSqlLog(logDto); 
		}
		
		return temp;
	}
	
	/**
	 * 获取订单对应的日志列表
	 * @param order_id
	 * @return
	 */
	public  static List<Dto> queryOrderLogList(String order_id) {
		List<Dto> logList= null;
		DBManager dbManager = new DBManager();
		try {
			if(CTUtils.isNotEmpty(order_id)){
				String sql = "select * from wxb_order_log t where t.order_id='"+order_id+"' order by t.createtime desc";
				logList= dbManager.queryForList(sql);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return logList;
	}
	
	/**
	 * 获取系统公告列表
	 * @param order_id
	 * @return
	 */
	public  static List<Dto> querySiteNewsList(String istop) {
		List<Dto> newsList= null;
		DBManager dbManager = new DBManager();
		try {
			if(CTUtils.isNotEmpty(istop)){
				String sql = "select * from wxb_news t where 1=1 " ;
				if(CTUtils.isNotEmpty(istop)){
					sql+=" and t.show_top ='"+istop+"' ";
				}
				
				sql+=" order by t.create_time desc,t.order_no";
				newsList= dbManager.queryForList(sql);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return newsList;
	}

}
